Measures in Pivot

[BobbyDollar]

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:

Scenario	Product	Year	Quarter	Volume
FC1	C3500	2019	1	5
FC1	C3500	2019	2	7
FC2	C3500	2019	1	6
FC2	C3500	2019	2	9

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.

	FC1	FC2 vs. FC1	FC2	FC2 vs. FC1
C3500	12	125%	15	125%

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:

	FC1	FC2	FC2 vs. FC1
C3500	12	15	125%

Any help appreciated.
Thank you

[Stachu]

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