PowerBI DAX Slicers, Calculate, SUMX 31 January 2020
Posted by David Wilson in General.9 comments
#PowerBI
I have a scenario that I’ve worked through recently that involved some out of the box thinking and a shallow dive into some DAX and the inner workings of SUMX.
The sample PBIX and datafile can be downloaded from here.
https://sites.google.com/site/dgwilson65/Industry%20Selection%20Fix.zip?attredirects=0&d=1
The user interaction with this Power BI report is to use the SELECT tab to select the Company they want to review some financial transactions for.
In the demo pbix file you can choose a company with the selected company being confirmed by the Measure output (yellow text on the black background).
The Company Name slicer is Sync’d on all subsequent tabs of the report – it’s shown on the SELECT tab and hidden on subsequent tabs.
Each company is associated with an Industry, so the intent is to show financial performance for all companies in the same industry as the selected company.
Picture-1 shows the data loaded to the Company table and which industry each company is associated with.
Picture-1
Picture-2 shows the PowerBI desktop where Company-M has been selected and the measure on screen confirms the selected Company.
Picture-2
Picture-3 shows the next tab where the hidden sync’d slicer restricts the displayed data to the company selected on the SELECT screen. It shows the financial transactions for the company across 5 months presented in a Matrix.
Picture-3
The third tab (Similar Industry) is where I want to display a summary of the financial transactions for all companies in the same industry as the company selected. Picture-4 shows what I want to see. The challenge is that the Sync’d Company slicer restricts reporting on the page to just the company selected in the slicer (i.e. the company on the SELECT tab). To deliver the required results a measure is required that calculates the result required.
Picture-4
What is required here is a Measure that calculates the value for each cell. It is a simple CALCULATE with a SUM and some filtering that lets me see all of the transactions for all companies.
First the Matrix. Importantly the columns Industry and Company Name. If these come from the company table they are restricted to showing only the company from the Sync’s slicer. This does not work for what is needed to be shown. To work around this I created a copy of the Company table called Company_unFiltered. The Matrix shows columns from this table. The unfiltered table has no relationship to any other tables (i.e. established in the datamodel). From the Modelling menu create a new table and use this DAX to create it.
Company_unFiltered = CALCULATETABLE(Company)
Picture-5
Now the DAX measure has the information required to calculate some numbers for us. DAX allows me to determine what the user has chosen in the slicer and further the Industry associated with that particular Company.
VAR industry_local = SELECTEDVALUE(Company[Industry])
And as the Measure calculates for each row and column we need to calculate an amount for each cell. To do that we need to know where we are and calculate that amount accordingly. To understand which row the measure is calculating the ISFILTERED DAX command is used.
VAR myGLCode = SELECTEDVALUE(Transactions[GL Code])
VAR myCompany = SELECTEDVALUE(Company_unFiltered[Company Name])
VAR amount 3 = IF(ISFILTERED(Company_unFiltered[Company Name]),
IF(ISFILTERED(Transactions[GL Code]),
CALCULATE(
SUM(Transactions[Amount]),
ALL(Company)
, Company_unFiltered[Industry] = industry_local
, Transactions[Company] = myCompany
, Transactions[GL Code] = myGLCode
)
After the calculation is complete the Measure returns a result. The result is only returned for Companies in the same industry. This means that if there is no data to present the column is not presented in the Matrix.
RETURN
IF(industry_local IN FILTERS(Company_unFiltered[Industry]),
amount3
)
The snippet of DAX presented above will calculate the SUM of transactions for a Company in the same Industry as the selected company and for each of the GLCodes as each row and column in the Matrix is iterated through. This is works well until you come to the total of the column and total of the row. Below is the full DAX measure used to provide the result. Examining it in more detail you can see that the ELSE conditions of the IF statement have an altered filter condition in the CALCULATE that remove the filter of the GLCode.
_Amount_4_Industry =
VAR industry_local = SELECTEDVALUE(Company[Industry])
VAR myGLCode = SELECTEDVALUE(Transactions[GL Code])
VAR myCompany = SELECTEDVALUE(Company_unFiltered[Company Name])
VAR amount3 = IF(ISFILTERED(Company_unFiltered[Company Name]),
IF(ISFILTERED(Transactions[GL Code]),
CALCULATE(
SUM(Transactions[Amount]),
ALL(Company)
, Company_unFiltered[Industry] = industry_local
, Transactions[Company] = myCompany
, Transactions[GL Code] = myGLCode
),
CALCULATE(
SUM(Transactions[Amount]),
ALL(Company)
, Company_unFiltered[Industry] = industry_local
, Transactions[Company] = myCompany
)
),
IF(ISFILTERED(Transactions[GL Code]),
CALCULATE(
SUM(Transactions[Amount]),
ALL(Company)
, Company[Industry] = industry_local
, Transactions[GL Code] = myGLCode
),
CALCULATE(
SUM(Transactions[Amount]),
ALL(Company)
, Company[Industry] = industry_local
))
)
return
IF(industry_local IN FILTERS(Company_unFiltered[Industry]),
amount3
)
All of that is rather messy and can be challenging to debug. There’s an easier way! The DAX function SUMX helps us. It is an Iterator function that works through a table Row by Row (refer to the separate note care of Matt Allington on SUM vs. SUMX).
SUMX handles the column totals automatically although management of the column and column totals still needs filtering. The DAX below is a lot less complicated.
[Update 3 February] – Marco Russo posted a comment correctly advising that SUM can be used within the CALCULATE statement. I’ve modified the code below for this. The text around this post has not been updated at this time.
_Amount_4_Industry_SUM =
VAR industry_local = SELECTEDVALUE(Company[Industry])
VAR company_local = SELECTEDVALUE(Company_unFiltered[Company Name])
VAR amount2 = IF(ISFILTERED(Company_unFiltered[Company Name]),
CALCULATE(
SUM(Transactions, [Amount]),
ALL(Company),
Company[Industry] = industry_local,
Company[Company Name] = company_local)
,
CALCULATE(
SUM(Transactions, [Amount]),
ALL(Company),
Company[Industry] = industry_local)
)
return
IF(industry_local IN FILTERS(Company_unFiltered[Industry]),
amount2
)
Picture-6 shows the final tab in the demo pbix file and both the results of the SUM and SUMX measures.
You can go back to the SELECT TAB and choose another company, from a different industry, return to this TAB and see the updated report based on the industry associated with the selected company.
Picture-6
Credit to Matt Allington
https://exceleratorbi.com.au/use-sum-vs-sumx/
– David
TrafficCamNZ with DarkMode support 7 January 2020
Posted by David Wilson in General.add a comment
New for 2020 TrafficCamNZ has been updated with the following enhancements and fixes:
● Minimum iOS support level is now iOS 11.0
● Supports Dark Mode and it rocks!
● Fixes an issue where the Camera name got progressively overwritten in the All Camera tab. It is clean and beautiful now.
● Fixes a preferences issue on iPad that prevented the Predictive mode from being turned on
● ChromeCast support upgraded for version 4.4.4 of the ChromeCast SDK to support iOS 13
A note about ChromeCast support
– it requires access to WiFi, Bluetooth, and Microphone – so TrafficCamNZ will request your permission to access these.
– Uses microphone access to listen for ultrasonic tokens when pairing with nearby Cast devices.
– Uses Bluetooth to discover nearby Cast devices.
– Uses WiFi to discover nearby Cast devices – and to access web cameras/traffic camera data for display
TrafficCamNZ will be available on your iOS device shortly via the standard AppStore Software Update.
– David
David Wilson
https://dgwilson.wordpress.com
https://nz.linkedin.com/in/dgwilson65
David's Opinions 