Totals doesn't match with matrix rows

[Sander1401]

Hi All,

I have a problem and I need your help. I am working on a calculations of PxQ (price x quantity). On rowlevel it works, but on total level it doesn't add up. The totals are much higher than the sum of the rows.
The calculation is as below:

Q avg last 2 months gives the average of the number of products of the last months (Finance wants it that way).

Q avg last 2 months =

VAR CurrentMonth =

    SELECTEDVALUE ( data[Date] )

RETURN

AVERAGEX (

    TOPN (

        2,                                            

        FILTER (

            ALL ( data[Date]),

           data[Date] <= CurrentMonth              

        ),

        data[Date], DESC

    ),

    CALCULATE ( SUM (data[Q] ) )                

)

P is the price.

PxQ = [Q avg last 2 months] * AVERAGE(data[P])

Can anyone help me out, so the totals match up with the sum of the rows, please?
For example: In the matrix the column on the right says 305.846,76, but the rows only sums up to 223.425,55

please find attached the link to my test dashboard:

https://www.dropbox.com/scl/fi/vfymixfzng0vllckiunl3/test_PxQ.pbix?rlkey=o852p2xqpht0e1hr2odywyl4p&s...

[Ashish_Mathur · Accepted solution]

Hi,

Drag this measure to the visual

Measure = SUMX(VALUES(data[productid]),[PxQ])

Hope this helps.

[Sander1401]

You are a lifesaver! Thank you very much!

[Ashish_Mathur]

You are welcome.

[m4ni]

Maybe filter you matrix and measure to the same rows to understand...

[m4ni]

@Sander1401 , this is not uncommon in Power BI.  Usually requires investigation at a more granular level.  Its difficult to respond with exact solution but I would suggest if you can look at a specific row or ID example to try and understand what the measures / totals are doing.  It may become evident like this.  HTH