Power bi divide formula is averaging out the result instead of Sum

Power BI
1

Hello Everyone,
Good Day!

I have a table as per the attached image where for price column i need to get sum, but instead it’s averaging out in power bi.

Pbix file is attached for your reference.

Any help is much appreciated on this.

Thanks,
Niraj

power%20bi1 power%20bi2 Test.pbix (39.2 KB)

1 Like
2

@nbaraili,

Just need to apply a bit of extra logic in the DAX measure to get the correct total. This revised measure should work for you:

Price Revised Logic = 

VAR BasePriceTable = // This creates a virtual table from which we can calculate the correct total price
    ADDCOLUMNS( 'Product',
        "BasePriceCol", [Price] // This is your measure
    )

VAR TotalBasePrice = // This calculates the sum of the price column in our virtual table
    SUMX( BasePriceTable, [BasePriceCol] )

RETURN  
 
IF( HASONEVALUE( 'Product'[Product] ),   // This tests whether the row being evaluated references a specific product or the total row, and applies the correct measure to each 
    [Price],
    TotalBasePrice
)

image

Here’s are a couple of videos where Sam explains this issue in detail:


Hope this is helpful.

  • Brian

P.S. thanks very much for providing the PBIX file and a clear explanation of the problem - makes it much easier to assist.

Full solution file attached:
Correcting Grand Total.pbix (45.0 KB)

2 Likes
3

Hi Brian,
Thank you very much.
It really help me a lot.