topic Re: Help Converting a Tableau FIXED Calc to PowerBI DAX in DAX Commands and Tips

Help Converting a Tableau FIXED Calc to PowerBI DAX

Anonymous — Fri, 12 Nov 2021 17:40:15 GMT

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:

Dol Share = Sum([Dol])/Sum({Fixed [Nielsen Market], [Period Description Short]: Sum([Dol])})*100
- Where 'dol' is a Continuous Measure
- Where 'Nielsen Market' is a Discrete Dimension/Attribute in String
- Where 'Period Description Short' is a Discrete Dimension/Attribute in String
- Where 'Brand Category' is a Discrete Dimension/Attribute in String

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.

Re: Help Converting a Tableau FIXED Calc to PowerBI DAX

smpa01 — Fri, 12 Nov 2021 18:38:52 GMT

Anonymous can you provide sample data and expected output?

Re: Help Converting a Tableau FIXED Calc to PowerBI DAX

Anonymous — Fri, 12 Nov 2021 20:58:12 GMT

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

Re: Help Converting a Tableau FIXED Calc to PowerBI DAX

smpa01 — Mon, 15 Nov 2021 16:33:51 GMT

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

Re: Help Converting a Tableau FIXED Calc to PowerBI DAX

smpa01 — Tue, 16 Nov 2021 19:43:46 GMT

Anonymous did you try the above?