Matrix total from 2 different tables is incorrect

[Salauat]

Hello, 

I will explain very simply. I have two tables with relationship "Model"

Table_A:

• Col1 - Model (multiple)
• Col2 - Model name

Table_B:

• Col1 - Model (unique)
• Col2 - Unit Price

My table visual shows me total price = unit price * model count, which is calculated correct. But the grand total for all devices is too high. I think here's something wrong with HASONEFILTER expression

My total price formula:

CALCULATE(SUM('Price'[Price]) * COUNTA('Model'[Model name]))

Here's sample data screenshot

I couldnt upload image, so I am putting link here - https://ibb.co/HCtdzXP

[CNENFRNL]

Hi, @Salauat , you may try this measure,

Total price =
SUMX (
    DISTINCT ( 'Table_B'[Model] ),
    CALCULATE ( COUNTROWS ( 'Table_A' ) * MAX ( 'Table_B'[Unit Price] ) )
)

[Salauat]

Hello @CNENFRNL . I tried your formula, but anyway overall total is not correct

[AntrikshSharma]

@Salauat Your columns are coming from different tables, show how they are connected, it would be great if you can attach a sample file.

[Anonymous]

@Salauat  try using HASONEVALUE instead. You can refer the following link which solves the measure totals problem:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Did I solve your problem?

If yes, then please mark my answer Accepted!

[Salauat]

@Anonymous I have tried your solution, but it is not working for me, maybe I typed incorrect formula.

Can you please write a formula for me?

[Anonymous]

@Salauat , you need to summarize the Measure you have created. The calculated measure for correct total will be as follows:

MeasureTotal =

VAR _sum = SUMMARIZE(TableA, TableA[Model Name], "_value", [Measure])

RETURN

IF(HASONEVALUE(TableA[Model Name]),[Measure],SUMX(_sum, [_value]))

Did I answer your problem?

If yes, please mark as Accepted!