Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Help Converting a Tableau FIXED Calc to PowerBI DAX

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:

 DolFixed Calculation DolDol Share
IL - STATE147,767,548147,767,548100.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:

 DolFixed Calculation DolDol Share
IL - STATE3,580,161147,767,5482.4
 

How could we translate this to DAX? We tried a SUMX but it does not hold denominator of the calculation stable.

 

 

1 ACCEPTED 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

 

smpa01_0-1636993888880.pngsmpa01_1-1636993907955.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

4 REPLIES 4
smpa01
Super User
Super User

@Anonymous  can you provide sample data and expected output?

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

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

 

smpa01_0-1636993888880.pngsmpa01_1-1636993907955.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@Anonymous  did you try the above?

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors