Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Community - We are trying to switch a report from Tableau to PowerBI and trying to convert a calculation that uses a FIXED level of detail calc to a DAX calculation but not able to get it to work right. Here's how it works in Tableau. It's a calculation to create 'share'.
Tableau Calcs:
Expected Output Example:
Here is the calculation in a Tableau view (Dol being the native continuous measure, displayed as a SUM). The Fixed Calculation being the denominator of the calculation from the above calculation. And the third column being the result. This first image shows what it looks like with no filters applied:
Dol | Fixed Calculation Dol | Dol Share | |
IL - STATE | 147,767,548 | 147,767,548 | 100.0 |
Then let's say we apply a Filter for brand, the Fixed calculation properly stays put on the total market value, but Dol filters down, thus providing us with the correct 'Share' calculation:
Dol | Fixed Calculation Dol | Dol Share | |
IL - STATE | 3,580,161 | 147,767,548 | 2.4 |
How could we translate this to DAX? We tried a SUMX but it does not hold denominator of the calculation stable.
Solved! Go to Solution.
@Anonymous sorry for late reply. Please find attached the pbix
The end goal can be achieved in two ways in DAX
_sumOfDol =
SUM ( 'Table'[Dol] )
_%ofDolShare1 =
DIVIDE (
[_sumOfDol],
CALCULATE (
SUM ( 'Table'[Dol] ),
ALLEXCEPT ( 'Table', 'Table'[Nielsen Market] )
)
)
_%ofDolShare2 =
VAR _1 =
CALCULATE ( SUM ( 'Table'[Dol] ), ALLSELECTED ( 'Table'[Nielsen Market] ) )
VAR _2 =
CALCULATE (
SUM ( 'Table'[Dol] ),
ALLSELECTED ( 'Table'[Nielsen Market] ),
ALL ( 'Table'[Category] )
)
RETURN
DIVIDE ( _1, _2 )
You can either select 1 market or multiple markets, the measure will perform accordingly
@Anonymous can you provide sample data and expected output?
Hi, sure! Here's a sample file. Using only the 'Dol' column to recreate the calculation in DAX. I would expect that we could get to the point where we can achieve a 'dol share' output looks like the pivot table in the attached file. We should be able to Filter to a single Market and then by a Category and see the correct Dol Share because the calculation is "fixed" at the Market level. Let me know if this is not a helpful sample file. First post the powerbi community - super new to DAX. Sample Data
@Anonymous sorry for late reply. Please find attached the pbix
The end goal can be achieved in two ways in DAX
_sumOfDol =
SUM ( 'Table'[Dol] )
_%ofDolShare1 =
DIVIDE (
[_sumOfDol],
CALCULATE (
SUM ( 'Table'[Dol] ),
ALLEXCEPT ( 'Table', 'Table'[Nielsen Market] )
)
)
_%ofDolShare2 =
VAR _1 =
CALCULATE ( SUM ( 'Table'[Dol] ), ALLSELECTED ( 'Table'[Nielsen Market] ) )
VAR _2 =
CALCULATE (
SUM ( 'Table'[Dol] ),
ALLSELECTED ( 'Table'[Nielsen Market] ),
ALL ( 'Table'[Category] )
)
RETURN
DIVIDE ( _1, _2 )
You can either select 1 market or multiple markets, the measure will perform accordingly
@Anonymous did you try the above?
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |