Forum Discussion

BobbyDollar's avatar
BobbyDollar
Frequent Visitor
6 years ago

Measures in Pivot

Hi

I use PowerPivot in Excel and am stuck with an issue. Because i am not sure with my DAX approach I post here, hoping somebody can help.

Sample date in DataModel:

 

ScenarioProductYearQuarterVolume 

FC1

C3500201915 
FC1C3500201927 
FC2C3500201916 
FC2C3500201929 


I create a Measurer to calculate the Scenario difference:

 

FC2 vs. FC1:=CALCULATE(SUM(Table[Volume];Scenario="FC2")/CALCULATE(SUM(Table[Volume];Scenario="FC1")

 


In Pivot I need to compare the different scenarios. So Rows = Product and Scenario = Column. If i pull my Measurer into the Values, the pivot creates 2 new columns, showing same data.

 FC1FC2 vs. FC1FC2FC2 vs. FC1
C350012125%15125%

 

But actually I need only one Column, at the end of pivot to have a comparison of the scenarios. Any advice how to do??

Hopefully not by creating a Set in Pivot

 

It should look like this:

 FC1FC2FC2 vs. FC1
C35001215125%


Any help appreciated.
Thank you

1 Reply

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    6 years ago

    I would create 3 measures

    FC1:= CALCULATE(SUM(Table[Volume];Scenario="FC1")
FC2:= CALCULATE(SUM(Table[Volume];Scenario="FC2")
FC2 vs. FC1:= DIVIDE([FC2];[FC1])

    I'd also recommend using the DIVIDE for comparison , as it has DIV/0 handling