Measure Total is incorrect where as column Total is correct in Matrix

[ashrat001]

Hi Experts,

I have a few columns and measures into my Matrix but measures total is being calculated incorrectly.

I would appreciate your assistance in this. Please see below a screen shot for your reference. Thanks!

Many thanks!

Tariq Ashraf

[SivaMani]

@ashrat001, Need additional info. What is the logic used in Measure Value?

Please refer to this thread for a quick response from the community - https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-Get-Your-Question-Answered-Quickly/m-p...

[ashrat001]

Hi Siva,

Thanks for your quick respone.

Following is my measure definition:

SD Selling Price M =
var totalSalesVolume = sum('Table'[Sales Volume Tons]) + sum('Table'[Fibre SalesTon Export])
var SalesVolumeSDprice = (sum('Table'[Sales Volume Tons]) * sum('Table'[SD Selling Price])) + (sum('Table'[Fibre SalesTon Export]) * sum('Table'[SD Selling Price Exp]))
return
if( totalSalesVolume <> 0, SalesVolumeSDprice/totalSalesVolume,0)

Thanks!

TA

[SivaMani]

@ashrat001, Can you provide the expected output and describe with an example like how it is calculated?

[SivaMani]

@ashrat001, Try the below logic,

SD Selling Price M =
var totalSalesVolume = sum('Table'[Sales Volume Tons]) + sum('Table'[Fibre SalesTon Export])
var SalesVolumeSDprice = (sum('Table'[Sales Volume Tons]) * sum('Table'[SD Selling Price])) + (sum('Table'[Fibre SalesTon Export]) * sum('Table'[SD Selling Price Exp]))

VAR Result = if( totalSalesVolume <> 0, SalesVolumeSDprice/totalSalesVolume,0)

return
IF(HASONEFILTER(Table[Month]),
Result,
SUMX(Table,Result)
)

For more details - https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

[ashrat001]

Hi Siva,

I need correct total of measures in the Matrix. Thanks!

TA

[ashrat001]

Hi Siva,

As per your reply, I've updated the mesaure definition.

SD Selling Price M =
var totalSalesVolume = sum('Table'[Sales Volume Tons]) + sum('Table'[Fibre SalesTon Export])
var SalesVolumeSDprice = (sum('Table'[Sales Volume Tons]) * sum('Table'[SD Selling Price])) + (sum('Table'[Fibre SalesTon Export]) * sum('Table'[SD Selling Price Exp]))
var result = if( totalSalesVolume <> 0, SalesVolumeSDprice/totalSalesVolume,0)
return
IF(HASONEFILTER('Black Fibre Line 1'[Month.Month Level 01]),
Result,
SUMX('Black Fibre Line 1',Result)
)

Unfortunately, I have not got correct total again. See below filter applied on Month and also output. 

Thanks!
TA

[SivaMani]

@ashrat001, If possible, share the pbix file with sample data for further analysis. 

[ashrat001]

Hi Siva,

I'm unable to attach pbix file for your reference at Power  BI community. Thanks!

TA

[SivaMani]

@ashrat001, You can put it in a file share like dropbox and share the URL

[SivaMani · Accepted solution]

@ashrat001, try this

SD Selling Price M =
SUMX (
    'Black Fibre Line 1',
    DIVIDE (
        ( 'Black Fibre Line 1'[Sales Volume Tons] * 'Black Fibre Line 1'[SD Selling Price] ) + ( 'Black Fibre Line 1'[Fibre SalesTon Export] * 'Black Fibre Line 1'[SD Selling Price Exp] ),
        ( 'Black Fibre Line 1'[Sales Volume Tons] + 'Black Fibre Line 1'[Fibre SalesTon Export] )
    )
)

[ashrat001]

Hi Siva,

Thanks for your assistance. Yes, it worked perfectly. What was wrong with my measure definition. Thanks!

TA

[SivaMani]

Aggregation level plays a role.

E.g.,

Row1 => (2*3) = 6

Row2 => (3*3) = 9

Total(based on your measure) => 5*6 = 30

Expected Total logic=> (2*3) + (3*3)  = 15

Hope it helps you to understand the behavior.

[ashrat001]

Hi Siva,

Thanks for your prompt reply. Following is the Google drive link for pbix file.

https://drive.google.com/drive/folders/1zAlDJ_oP-rV7iTHAs5JSlxJM9im64l5N?usp=sharing

Many thanks!

TA