BI-RoundUp – Power BI (Service & Mobile Update – Create Power BI reports from Excel & CSV Files in SharePoint Online)

Here is the weekly BI-RoundUp, so let’s get into it

Power BI – Service & Mobile Update

As you can see above there now is an option to share a report with the current filter and slicers that are enabled. This is great because it will mean that you can have a subscription report that will send it out every time with the applied filters and slicers.

There is the updated Gateway connections a lot easier with greater visibility to show you what you need to do to get it working.

The On-Premise Data Gateway has been updated to the June Edition of the Mashup Engine.

And finally, for the Mobile Report canvas they have increased the amount of tiles to be larger and longer.

You can find more details here: Power BI Service and Mobile June Feature Summary

Power BI – Create Power BI Reports from Excel or CSV Files in SharePoint Online

I was not aware that there have been updates in SharePoint online, where if you have got an Excel file with a table formatted or a CSV file, there is the option to now Open in Power BI.

This will then open it in Power BI and automatically be connected to your SharePoint Online files. Which in turn means when you update the files on SharePoint online, it will automatically be updated in the Power BI Service without having to use the On-Premise Data Gateway.

You can find the full blog details here: New feature: Create Power BI reports from tables in Excel files and CSV files

Creating a Slicer that also contains a DAX Measure

I got an interesting question from Vijay asking, “How do we do a slicer with data count?”

This is what Vijay was looking to achieve as shown below and I always enjoy a good challenge.

As I always like to do, is to show how I got to the required result.

  • I currently have got data from my website, which I look at daily to see how my blog posts are tracking.
  • In my dataset I like to see where in the world people are coming from to visit my blog or website.
  • Based on the requirement from Vijay I created a slicer which takes the total sessions per country, which I can then use as a Slicer on my data.

The first thing that I did was to create a calculated table which will have the information I require for the Slicer.

In Power BI Desktop I went to the Modeling Ribbon and clicked on New Table

This will be the Country and Sessions. I created it with the following DAX Syntax below.

Slicer Table =
    ADDCOLUMNS (
        SUMMARIZECOLUMNS ( 'All Web Site Data'[Country] ),
        "Sessionszz", [Sessions]
    )
  • Line 1
    • What I have done above is created a table called Slicer Table
  • Line 2-3
    • This is where I added the ADDCOLUMNS Syntax for my table.
    • NOTE: Thanks to Maxim for commenting and letting me know that I did not need the original CALCULATETABLE
  • Line 4
    • I then used the SUMMARIZECOLUMNS DAX function and put in the ( ‘All Web Site Data'[Country] column, as per my requirement to get a Count of Sessions per Country.
  • Line 5
    • This is where I created my Column Name called “Sessionszz” and my measure called [Sessions]
    • NOTE: When I use measures I only use the Square brackets.
  • Line 6-7
    • I then close off my previous DAX functions.

The result is I now have the following calculated table.

How I had the data in order to create a new calculated column which will have the Country with the Total Sessions. I did it with the following DAX Syntax below.

Country with Sessions =
'Slicer Table'[Country] & " ("
    & 'Slicer Table'[Sessionszz]
    & ")"

 

And the result was I now had my new column in my table showing both the Country and the Total Sessions

Next, I created a relationship from my Fact table ‘All Web Site Data’ to my new calculated table called ‘Slicer Table’ on the Country Column.

I went to the Modeling tab again, and this time clicked on Manage Relationships

I then clicked on New and selected the following as shown below.

I then click Ok and Ok again to come back to Power BI Desktop

Now I was able to create the Slicer, which shows the Country and the Total Sessions.

I have selected the Country “Afghanistan” which has got a total of 4 sessions, and in my table, I can see the sessions and the date when they occurred.

Each time the dataset is refreshed the calculated table will also be refreshed which will ensure that the slicer values are up to date.

This was a great exercise and I personally think something quite handy to have when slicing data.

As always if you have any questions or comments please leave them in the area below.

Creating a DAX Calculated Column for Today, Yesterday and Next Working Day with Variables

Below was a question from the Power BI Community where the output was to have a calculated column in the Date Table, which would have Today for Todays date, Yesterday for Yesterdays Date, and Next Working Day (Being the following Monday)

The challenge was that for the “Next Working Day” it should only be for the next Monday and not all Mondays.

If you did not know you can use DAX Variables not only in DAX measures, but also in DAX Calculated columns.

I would like to mention that I had always like to step out my calculations when working in long or complex DAX measures or calculated columns. This video from Guy In a Cube with Patrick and Marco gives a great introduction on how to Debug DAX, in which they demonstrate how to use variables in DAX measures to debug the measures.

So below is my DAX calculated column with the explanation afterwards.

T/Y/Tom =
VAR TodaysDate =
    TODAY ()
VAR YesterdayDate =
    TODAY () - 1
VAR NextMondaysDate =
    SWITCH (
        TRUE (),
        'Date'[Date] - WEEKDAY ( 'Date'[Date], 2 )
            + 1
            >= TodaysDate, 'Date'[Date] - WEEKDAY ( 'Date'[Date], 2 )
            + 1,
        TODAY () - 9
    )
VAR NextWorkingDay =
    SWITCH (
        TRUE (),
        'Date'[Date] = TodaysDate, "Today",
        'Date'[Date] = YesterdayDate, "Yesterday",
        'Date'[Date] = NextMondaysDate, "Next Working Day",
        "Not Applicable"
    )
RETURN
    NextWorkingDay

Code above formatted with the DAX Formatter

  • Line 1 – This is where I am giving my Calculated Column a name called T/Y/Tom meaning Today/Yesterday/Tomorrow
  • Lines 2 & 3 – This is where I am creating a variable TodaysDate which today’s date using the DAX function TODAY()
    • With today’s date being 02 July 2018
  • Lines 3 & 4 – This is where I am creating a variable called YesterdayDate getting yesterday’s date, going back one day from today’s date.
  • Lines 6-8 is where I am creating a variable called NextMondaysDate.
    • I am then using the DAX SWITCH function which I find personally is a lot easier to use than an IF function.
    • My experience has also taught me that often an initial requirement is for two possible conditions, but once it is looked at more closely there are more than two conditions. And using the combination of the SWITCH and TRUE function makes it a lot easier to add in as many conditions are required.
  • Lines 9 & 10 – This line is quite interesting, because what I wanted to do was to find what Next Mondays date would be.
    • And if I had to put this single calculated column into my dataset I would get the following as shown below.
    • I actually did this as part of my debugging process to ensure that I was getting the right date.
    • As you can see above what this does is it gives me the date for each Monday in each Week.
      • For this current week the date for Monday is 07/02/2018
      • Whilst for next week the date for Monday is 07/09/2018
  • Lines 11 & 12 is where I am comparing to see if it is greater than equal to todays date.
    • If it is then use the Mondays Date.
    • In this example the output would appear to be the same as the DAX in lines 9 & 10.
    • The difference here is if you had to use it outside of the SWITCH context it would then result in every Monday having the “Next Working Day” which is not the required result.
  • Lines 13 & 14 – This is where I am putting in the ELSE condition which is set to take Today’s date and go back 99 999 days. Which should not be seen in the dataset.
  • Lines 15 – 22 – This is where once again I am using the SWITCH and TRUE DAX functions, because I have got multiple conditions to evaluate.
    • The first condition is where I am looking at my date table and where my calculated DAX column matches my variable for today.
      • If you had to not imagine it, but see what it would look like, it would look like the following:
        • ‘Date'[Date] = “07/02/2018”, “Today”,
    • The second condition is where I am looking at my date table and where my calculated DAX column matches my variable for yesterday.
      • If you had to not imagine it, but see what it would look like, it would look like the following:
        • ‘Date'[Date] = “07/01/2018”, “Yesterday”,
    • The third condition is where I am looking at my date table and where my calculated DAX column matches my variable for next Monday.
      • If you had to not imagine it, but see what it would look like, it would look like the following:
        • ‘Date'[Date] = “07/09/2018”, “Next Working Day”,
  • Lines 23 & 24 – This is where I close off my variables and use my final variable called NextWorkingDay

So now when I look at my date table I see the following below which what is I wanted to get.

As you can see above there is a previous Monday’s date, and it says Not Applicable, so I am getting the results I expect.

I hope you found this blog post useful on how to use DAX variables in DAX calculated columns, as well as how to use it for debugging your DAX code.

As always if you have got any questions or suggestions please leave them in the comments section below.

DAX – Concatenating Values only when values are selected

What I am doing with the DAX below is to only show the concatenation of values if a value is filtered. If nothing is filtered (Selected) then display text to show that nothing has been selected, otherwise display in a concatenation what has been selected.

NOTE: If you individually have to select each item, it is still being filtered so it will show all the values concatenated.

Selected User =
VAR ConCat =
    CALCULATE (
        CONCATENATEX (
            VALUES ( 'Power BI Audit Log'[User] ),
            'Power BI Audit Log'[User],
            ","
        )
    )
VAR IsItFiltered =
    IF ( ISFILTERED ( 'Power BI Audit Log'[User] ), ConCat, "No Users Selected" )
RETURN
    IsItFiltered

 

  • What is happening above with the Variable called “ConCat” is where I am concatenating the items from the ‘PowerBI Audit Log’ table and using the [User] column.
  • Then in the next Variable called “IsItFiltered” I am using the IF and ISFILTERED DAX function to evaluate if the [User] column is being filtered.
    • If it is being filtered then return the previous Variable called “ConCat” otherwise return “No Users Selected”
  • And the final piece is where I am returning the output from the IsItFiltered Variable
  • This is what it looks like when No users are selected
  • And then when two users are selected

As always if there are any questions please leave them in the comments below.

Reactivated Players DAX Pattern

I had a requirement where my customer was looking to gain some insights into their data relating to reactivated players.

The definition used for a reactivated player is a player that has previously played but has not played for a certain number of days. The player then comes back and plays again. So, by this definition the player is reactivated.

What I had to do, to get this working so that it performed well was to put this into a calculated table. This ensured that I only had the data I required. Due to the sheer volume of data that I had to go through, trying to do this with a DAX measure proved in my example to be very slow. And I would rather create a calculated table which can perform much faster and give a greater reporting experience.

I explain below. It is quite Complex so please read carefully below with the colour coded sections

Overview: What this calculated table is doing, is counting how many days the player made an order between his last order and second to last order based on date.

NOTE: I am going to explaining this from the inside out.

Using either Power BI Desktop or Azure Analysis Services or SSAS Tabular 2017, I created a Calculated with the following syntax below.

  1. Reactivated Players Table =
  2. FILTER (
  3. CALCULATETABLE (
  4.    ADDCOLUMNS (
  5.     VALUES ( ‘Adventure Works'[Customer Name] ),
  6.      “Days Between Orders”
  7.       , CALCULATE (
  8.         DATEDIFF (
  9.          CALCULATE (
  10.           MAX ( ‘Adventure Works'[Order Date] ),
  11.           FILTER ( ‘Adventure Works’, ‘Adventure Works'[Order Date] <> MAX ( ‘Adventure Works'[Order Date] ) )
  12.          ),
  13.          MAX ( ‘Adventure Works'[Order Date] ),
  14.         DAY
  15.        )
  16.     ),
  17.   “Last Order Date”, CALCULATE ( MAX ( ‘Adventure Works'[Order Date]), ALLEXCEPT ( ‘Adventure Works’, ‘Adventure Works'[Customer Name] ) ),
  18.   “GeographyKey”, CALCULATE (
  19.         MAX ( ‘Adventure Works'[GeographyKey] ),
  20.         ALLEXCEPT ( ‘Adventure Works’, ‘Adventure Works'[Customer Name] ) )
  21.     )
  22.  ),
  23.  [Days Between Orders] <> BLANK ()
  24. )

          Lines 8 – 11
    • This DAX Calculation is getting the second to last date the player made an order.
      • This could be called “Second to Last Order Date”
    • Line 12
      • This DAX calculation is getting the last date the player made an order.
        • This could be called “Last Order Date”
      • Lines 7-8 & 13-15
        • This is the outer DAX CALCULATE which is using the DATEDIFF to count how many Days it was between the two dates above.
        • EG: DATEDIFF(“Second to Last Order Date”,” Last Order Date”,DAY)
      • Line 6
        • This is the name of the column that I am creating in my Calculated Table called “Days Between Orders”
        • Line 5
          • Here I am getting the VALUES for the column which is the Customer Name
          • NOTE: It is important here to always use the Customer Name or CustomerAccountKey in order to get back the values you are expecting
        • Line 17
          • I am creating another column called “Last Order Date” with another DAX measure
          • This column is getting the last time the player made an order. And to do that I had to use the ALLEXCEPT so that it would break the filter context of the entire table and do it for each Customer Name
          • The reason for this column is so that I can join this back to the Date table in my dimension model
          • NOTE: This is so that I can then use the Date table to filter multiple tables when accessing the reports.
        • Line 18 – 21
          • Once again I am creating another column called “GeographyKey” with a DAX Measure
          • I created this column, so that I can get the GeographyKey for each Customer Name. And to do that I had to use the ALLEXCEPT so that it would break the filter context of the entire table and do it for each Customer Name
          • The reason for this column is so that I can join this back to the Geography table in my dimension model
          • NOTE: This is so that I can then use the Geography table to filter multiple tables when accessing the reports.NOTE: If I wanted to create any more columns to link back to the Dimension tables they would be done in this section.
        • Lines 3-4 & 22-23
          • This is where I am specifying to ADDCOLUMNS to my CALCULATETABLE syntax. Which creates the in-memory table.
        • Lines 2,24-25
          • The final piece is what makes this table so fast. Is I am then filtering my entire table. This is so that I do not bring back records that I am not interested in.
          • I do this specifically in line 24, where I am using the column I created in lines 6 – 16 and filtering it out to say where the Days are NOT BLANK

Apologies for the formatting above, I just could not get it the way it was in Word!

Once the above is done, I then get back a subset of all the records, which then makes this table very fast to query.

Creating the Dynamic Days Between Reactivation table

To give the required flexibility to the client, I created a table using the What-If Parameters.

This then allowed the client to dynamically decide how many days there should be for a player to be deemed as Reactivating.

I then created a slicer for this table

Reactivated Players

This measure is created off the table created above. Which makes it run correctly. As well as being super-fast when being queried.

Reactivated Players = 
VAR SelectedValue =
    SELECTEDVALUE ( 'Days between Reactivation'[Days between Reactivation], 30 )
RETURN
    CALCULATE (
        COUNTROWS ( 'Reactivated Players Table' ),
        'Reactivated Players Table'[Days Between Orders] >= SelectedValue
    ) 
  • In this measure above, I am once again using the Days between Reactivation Slicer for the end user to determine how many days decide how long a player is re-activated from.
  • Then I am counting the rows where it is less than the Number of days between the bets.

Report in Action

Below is what the report looks like in action, where the user is using the Slider in order to determine how many players are reactivating.

NOTE: The reason that I put in the column called “Reactivated Players” is so that it shows that the measure is working as expected. So if the Slicer is set to 30 any player with “Days Between Orders” of less than 30, should not have a 1 next to them. And as the slider goes up, the players with the corresponding “Days Between Orders” should decrease or show less “1”

 

 

Conclusion

I have shown how to create the reactivated players DAX pattern. As well as allowing the user to dynamically select the period for reactivation.

As always, I welcome any comments or suggestions.

Here is a link to the PBIX file: FourMoo – Reactived Players.pbix

Power BI – One Measure dynamically made up from multiple measures (To show Month Estimate for current Month)

Where I am consulting there was a requirement where for the current month they wanted to display the Current Month Estimate for the current month they were in, and to show the historical sales for all the previous months.

This makes some sound business sense, since currently month would typically start off with a lower sales amount, which would increase as each day goes by. Whilst the calculation for the Month Estimate is not very complex it does show if the sales are inline of where they should be.

In my example below, I am going to demonstrate how I did this, so that to the end user it appears to be one measure, even though I am using a few techniques for it to be displayed correctly.

Working Example

In this example I have got some sales data, and I am going to demonstrate how to create the Month Estimate.

I will then create the [Sales Amount] Measure, and the [Current Month Estimate] Measure.

The [Current Month Estimate] is made up by this simple calculation:

  • Sales for the current month I am in.
  • Get the Days Gone in the current month I am in.
  • Get the Total Days for the current month that I am in.
  • For example, if my current date is 09 December 2017, then I would have the following details below.
    • Sales for Current Month = $36,819.60
    • Days Gone in Current Month = 9
    • Total Days in Current Month = 31

And then finally, I will show how create the magic measure, which will show the Sales up until the previous month (Nov 2017), and then for the current month (Dec 2017), it will display the measure [Sales Inc Est] which will be renamed to Sales Amount.

Creating the standard measures

Below I created the following measures:

Sales Amount = SUM('Orders'[Sales])
Days Gone in Curr Mth = 
VAR LastDataDate =
    MAX ( 'Orders'[Order Date] )
RETURN
    DAY (
        DATE ( YEAR ( LastDataDate ), MONTH ( LastDataDate ), DAY ( LastDataDate ) )
    )
Days in Current Month = 
VAR LastDataDate =
    MAX ( Orders[Order Date] )
VAR EndOfCurrMonth =
    EOMONTH ( LastDataDate, 0 )
RETURN
    DAY ( EndOfCurrMonth )
Current Month Estimate = 
DIVIDE ( [Sales Amount], [Days Gone in Curr Mth] ) * [Days in Current Month]
Mth Est (Inc Prev Sales) = 
VAR LastDataDate =
    MAX ( 'Orders'[Order Date] )
VAR Start12MonthsAgo =
    DATE ( YEAR ( LastDataDate ) - 1, MONTH ( LastDataDate ) - 1, 1 )
VAR LastDatePreviousMonth =
    EOMONTH ( LastDataDate, -1 )
VAR SelectedSalesPrevMonths =
    CALCULATE (
        [Sales Amount],
        FILTER (
            'Date',
            'Date'[Calendar Date] >= Start12MonthsAgo
                && 'Date'[Calendar Date] <= LastDatePreviousMonth
        )
    )
VAR SelectedMthEst = [Current Month Estimate]
RETURN
    VALUE (
        IF (
            ISBLANK ( SelectedSalesPrevMonths ),
            SelectedMthEst,
            SelectedSalesPrevMonths
        )
    )

The final measure is the one that I feel needs the most explaining, which I will attempt to do below, and I hope it makes sense to you the readers, as it does make sense to me!

  • In lines 2 & 3 is where I am finding creating a Variable LastDataDate which is the last or max Order Date.
    • The reason for this is often data is not always as fresh as it could be. Meaning that it is often a day behind or if some other process upwind has failed it could be a few days behind. This ensures that I am getting the max Order data from my dataset.
  • Lines 4 & 5 is where I am creating a variable Start12MonthsAgo that will be the First date of the month from 12 months ago, based on my Variable LastDataDate
    • Here I used the DAX DATE function to get the date, and based on my data this would return the following date based on the 09 Dec 2017
  • Lines 6 & 7 is where I am creating the variable LastDatePreviousMonth which will store the last date of the previous month, based on my Variable LastDataDate
    • Here I used the DAX EOMONTH function to return the date based on 09 Dec 2017
  • Lines 8 to 16 is where I am creating the variable SelectedSalesPrevMonths for the Sales for the previous months prior to the current month I am in.
    • As with this example I want to get all the sales using the two variables I created Start12MonthsAgo (11/01/2016) and LastDatePreviousMonth (11/30/2017)
    • As you can see below this is the virtual values which will be stored as part of this variable
  • Line 17 is where I am mapping the previous measure [Current Month Estimate] to the variable SelectedMthEst
    to make it easier for readability.
  • And finally, on lines 18 to 25 is where I am using all the above logic to dynamically decide which measure will be for the previous 12 months, excluding the current month. And then where the SelectedMthEst
    will go.
    • One line 19 I must define it has a VALUE which ensures that the measure returns it as a Number value and not a Text value.
    • Line 20, is where I use an IF Statement to determine what variable to display based on the conditions I specified in my variables.
    • Line 21, is where I am evaluating the ISBLANK based on my variable SelectedSalesPrevMonths
      • This is where it might become a bit confusing to some people, because virtually when I create the table there will be no value for the current month for the SelectedSalesPrevMonths measure because it stops at the end of Nov 2017.
      • In the table context where I have got dates going past Nov 2017, the measure SelectedSalesPrevMonths will have BLANK values.
    • Line 22, is where if the measure SelectedSalesPrevMonths is indeed BLANK, then use the measure SelectedMthEst
      which once again is for the current month.
    • And finally, in line 23, is where the IF condition is evaluated to be FALSE, then use the measure SelectedSalesPrevMonths
    • After which I close all the required DAX functions.
  • Hopefully this image below explains it better below, where I have visually shown where the SelectedSalesPrevMonths ends and where the SelectedMthEst starts

Measure in Action

Now that I have gone through and explained how it all works, it is best to see it in action.

  • Let’s suppose that the current date was the 11 Dec 2017, this would then show the entire sales as shown below, including the [Current Month Estimate] measure, where it currently is estimating a good month for Dec 2017
  • Whilst if it had to be later in the month say just before the break on 22nd December 2017 it would then reflect the following below. Which is now showing that potentially sales are down.
  • And as you can see above it shows quite a different picture to the previous one.

Conclusion

What I have demonstrated here, is how to use variables and multiple measures, so that to the end user it appears as one measure. Not only that, but it completes their exact requirement, and can give them a quick indication on how their sales are going as the month progresses.

As always, any comments or suggestions are most welcome in the comments below.

Reference creating the date table: Power BI – How to Easily Create Dynamic Date Table/Dimension with Fiscal Attributes using Power Query

Here is a link to the Power BI file: Substituting Measures.pbix

DAX – Filtering Measure to show value when selected or no values selected

I had a requirement where I had a measure that I wanted to be filtered if it’s value was selected
then it must show the value. As well as if nothing is selected on the slicer then I also
want the value to be displayed.

I did this so that when the users are using the report, it will clearly show them what values they are looking at in the table. By making it clearly visible
once they selected a value from a slicer it is easier for the end users to get the right data.

This is best shown with an example below.

  • Current behavior is when I click on Day Shift it shows me the values for the Day Shift.
    • NOTE: In the above the 24hr Total does
      change to correctly reflect the Day Shift values.
  • Now when I click on Night Shift, I still see the values for Day Shift
    • NOTE: In the above the 24hr Total does change to correctly reflect the Night Shift values.

So below is the DAX that I used to overcome this challenge so that it would display correctly.

  • Here is the syntax with an explanation afterwards
    Day Shift =
        IF (
          ISFILTERED ( 'Prod'[Shift] )
            && VALUES ( 'Prod'[Shift] ) = "Day Shift"
           || NOT ( ISFILTERED ( 'Prod'[Shift] ) ),
       CALCULATE ( [UnitType (Selected)], 'Prod'[Shift] = "Day Shift" ),
     0
    )

  • What is happening above is I have got an IF Statement and what I am doing is first to see if the column is being filtered. This uses the ISFILTERED and is highlighted in PURPLE above.
  • Next is where I am also looking to see if the “Day Shift” has been selected from the Slicer, this is highlighted in ORANGE
  • I put in an OR “||” Condition, and I used the NOT which is then saying it is NOT FILTERED, this is so that if neitherDay Shift” or “Night Shift” is selected then also make this part of the IF Statement TRUE, this is highlighted in GREEN
  • Now that I had completed the Condition for my IF statement I then put in my calculation if it is TRUE, which is highlighted in BLUE
  • And then finally if the Slicer has “Night Shift” selected make the value for “Day Shift” equal 0

So it is easier to see it in action as shown below.

  • Here is showing where nothing is selected on the Slicers it shows all the values.
  • Next if I selectDay Shift” I want it to show the values for Day Shift, but make “Night Shift” zero. As well as the 24hr Total should only show the Day Shift values.
  • And finally, if I selectNight Shift” I want it to show the values for Night Shift, but make “Day Shiftzero. As well as the 24hr Total should only show the Day Shift values.

Conclusion

As you can see above I have demonstrated how to leverage DAX measures so that it will display the values in a way that is meaningful to the end users of the report.

This will help them easily understand the data that they are looking at, and not have to second question or double check to see if the figures are the ones that they are expecting to see.

Power BI – Dynamically changing the Title based on your selection

Would it not be nice if your Title changed based on what you selected on your slicer?

In this example below I am going to show you how I used a Country slicer to dynamically change the title of my card based on my slicer selections.

This was really useful because the Country Slicer has so many options I am not able to view all the Countries selected on the screen. And by doing it in this way it allowed me to create a more meaningful dashboard, as well as know which Countries had been selected.

As you can see below, this is what it looked like after I had added in my DAX measure and configured the Card. You can click on the Country Slicer to see the changes.

(more…)

Power BI – DAX Measures for Excel based “ % of Column Total “ or “ % of Row Total ”

Where I am currently consulting there was a requirement to create a measure like you can in the Excel pivot tables for the % of Column Total or the % of Row Total.

One of the things that you can currently do in Power BI, which I only learnt as part of this requirement is that you can use % of Column Total, or % of Row Total when using a table, as seen below.

But the thing is that as soon as you put that into a visual the only option that you have is the Percent of grand total.

So below are the two measures that I created so that I could then successfully have a % of Column Total (Percent of column total) or % of Row Total (Percent of row total)

(more…)

DAX – Getting the Previous Year to Date Value up to and including the Current Month Selected – Not the entire Previous Year

So for this example it is best explained with an image below, then some additional context afterwards

  • If you look above we have got the [Sum of Sales] for Dec 2010-11
    • NOTE: This I selected in the Slicer on the right hand side.
  • Then I have created a Year to Date (YTD) measure which is the running total for the Year
    • NOTE: This is the measure below.

      YTD-MTD-CY = TOTALYTD([Sum of Sales],’Date'[Calendar Date],”06/30″)

  • As you can see for Dec 2010-11 with the measure [YTD-MTD-CY] I have the Year to date value.

Now the requirement was if the user selected any Fiscal Month (EG: Feb) from the Slicer, they are looking for the Feb 2009-10 value for the Previous Year (Feb 2009-10). So once again if I show this in a picture this is the value that I am looking to get.

(more…)