Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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.

 

 

  • smpa01's avatar
    smpa01
    4 years ago

    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

     

     

4 Replies

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous  can you provide sample data and expected output?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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 

      • smpa01's avatar
        smpa01
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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