Totals in matrix not correct

[Anonymous]

Hi all,

I'm currently working with the following dataset:

Date	End Customer	Channel	Distributor	Region	Product	Units	Price	Revenue
01/03/2020	A	Wholesaler	A Ltd	UK	P001	10	2.5	25
01/03/2020	B	Trade	A Ltd	UK	P001	20	3.1	62
01/03/2020	C	Domestic	A Ltd	UK	P002	0	0	0
01/03/2020	D	Wholesaler	B Ltd	ROI	P002	40	1.8	72
01/03/2020	E	Trade	B Ltd	ROI	P002	0	0	0
01/03/2020	F	Domestic	B Ltd	ROI	P002	60	1.6	96
01/03/2020	G	Commercial	B Ltd	ROI	P003	50	2.7	135
01/03/2020	H	Trade	C Ltd	France	P003	4	1.54	6.16
01/02/2020	A	Wholesaler	A Ltd	UK	P001	8	2.45	19.6
01/02/2020	B	Trade	A Ltd	UK	P001	0	0	0
01/02/2020	C	Domestic	A Ltd	UK	P002	15	3.1	46.5
01/02/2020	D	Wholesaler	B Ltd	ROI	P002	10	1.95	19.5
01/02/2020	E	Trade	B Ltd	ROI	P002	5	2	10
01/02/2020	F	Domestic	B Ltd	ROI	P002	0	0	0
01/02/2020	G	Commercial	B Ltd	ROI	P003	12	2.95	35.4
01/02/2020	H	Trade	C Ltd	France	P003	10	1.54	15.4
01/01/2020	A	Wholesaler	A Ltd	UK	P001	20	2.6	52
01/01/2020	B	Trade	A Ltd	UK	P001	30	3	90
01/01/2020	C	Domestic	A Ltd	UK	P002	2	3.4	6.8
01/01/2020	D	Wholesaler	B Ltd	ROI	P002	25	2	50
01/01/2020	E	Trade	B Ltd	ROI	P002	16	1.5	24
01/01/2020	F	Domestic	B Ltd	ROI	P002	5	1.82	9.1
01/01/2020	G	Commercial	B Ltd	ROI	P003	0	0	0
01/01/2020	H	Trade	C Ltd	France	P003	22	1.1	24.2

I'm trying to calculate the price variance by "Channel" from one month to the next (01/03/2020 vs 01/02/2020). In the table below, you can see the result I'm getting for [Price Var]:

The dax measure is as follows:

Price Var = 
VAR Intervals = MonthsPrior[MonthsPrior Value]
VAR PricePriorPeriod =
CALCULATE(
    [Average Price],
    DATEADD( Data[Date], (Intervals *-1), MONTH)
)
RETURN
IF(
    ISFILTERED( Data[Channel] ),
    ( [Average Price] - [Price Prior Period] ) * SUM( Data[Units] ),
    SUMX(
        Data,
        (Data[Price] - PricePriorPeriod) * Data[Units]
    )
)

The totals for each individual channel (line totals) are correct... however the grand total at the bottom is incorrect. The grand total should be a sum of each Channel's total. i.e:
-12.5 + -90 + 27.52 + -11.61 = -86.59

The total I'm looking for in the grand total line is -86.59

Additional measures for reference:

Average Price

Average Price = 
DIVIDE( 
    SUM(Data[Revenue]), 
    SUM(Data[Units] )
)

Price Prior Period

Price Prior Period = 
VAR Intervals = MonthsPrior[MonthsPrior Value]
RETURN
CALCULATE(
    [Average Price],
    DATEADD( Data[Date], (Intervals *-1), MONTH)
)

Any help would be very much appreciated!
Thanks,
Aaron

[Greg_Deckler]

@Anonymous This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[Anonymous]

Hi @Greg_Deckler ,

Thanks for responding so quickly!

I updated my measure to this:

Price Var = 
IF(
    HASONEFILTER( Data[Channel] ),
    ( [Average Price] - [Price Prior Period] ) * SUM( Data[Units] ),
    SUMX(
        Data,
        (Data[Price] - [Price Prior Period]) * Data[Units]
    )
)

However, I'm still not getting the expected result. My result becomes 396.16 instead of -86.59.

I think this probably has something to do with the fact that I am trying to subtract the current period price (this is defined by the user in a date filter on the page) by the price in the period prior to the selected period.

If you take a look at the screenshot below, I have defined the period as 01/03/2020 and the "MonthsPrior" parameter is set to 1, therefore the (Prior Month) measures are displaying the figures for 01/02/2020. The actual line by line results for Price Var are correct, just not the total! 😞

Any thoughts?

Aaron

[Greg_Deckler]

@Anonymous 

You need to use SUMMARIZE or GROUPBY based upon how you are grouping things in the visual:

Price Var = 
IF(
    HASONEFILTER( Data[Channel] ),
    ( [Average Price] - [Price Prior Period] ) * SUM( Data[Units] ),
    SUMX(
        Data, //this generally needs to be a SUMMARIZE or GROUPBY of the table based upon how data is grouped/summarized in the table/matrix
        (Data[Price] - [Price Prior Period]) * Data[Units]
    )
)

[Anonymous]

Hi @Greg_Deckler 

I tried the following but I'm getting an error:

"The CALCULATE function cannot be used in an expression argument for the GROUPBY() function."

Price Var = 
IF(
    HASONEFILTER( Data[Channel] ),
    ( [Average Price] - [Price Prior Period] ) * SUM( Data[Units] ),
    GROUPBY(
        Data,
        Data[Channel],
        "Price Variance", 
        SUMX(
            CURRENTGROUP(),
            ( Data[Price] - [Price Prior Period] ) * Data[Units]
        )
    )
)

Is this because I'm trying to use the [Price Prior Period] measure which uses CALCULATE?

Price Prior Period = 
VAR Intervals = MonthsPrior[MonthsPrior Value]
RETURN
CALCULATE(
    [Average Price],
    DATEADD( Data[Date], (Intervals *-1), MONTH)
)

[Greg_Deckler]

@Anonymous Yet another reason I do not like CALCULATE, or T"I" functions. 

https://community.powerbi.com/t5/Community-Blog/CALCUHATE-Why-I-Don-t-Use-DAX-s-CALCULATE-Function/ba-p/1248635

https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

[Anonymous]

@Greg_Deckler 

Any advice how I would therefore update my measure to make it work? Given the dataset above etc?

[Greg_Deckler]

@Anonymous Well, maybe try SUMMARIZE instead of GROUPBY but otherwise would need to mock it up and get rid of T"I" and CALCULATE functions using something like AVERAGEX and whatever FILTER is necessary to get rid of DATEADD (like EOMONTH, etc.) Would take some time to unwind. Would need all of your formulas. Any chance you can share PBIX to speed things up?

[Anonymous]

@Greg_Deckler 

Of course!

See link below:

https://www.dropbox.com/s/4407b0xy91u9jtn/Price%20Variance%20Analysis.pbix?dl=0

[Greg_Deckler]

@Anonymous OK, I've got it downloaded, I'll try to review today and see if I can get it unwound.

[Anonymous]

You are a hero! Thank you! 😊