How to Make Measures Total Correctly in Power BI Tables

[revmac]

It seems that fixing incorrect totals in Power Bi is a common problem.

Nevertheless, none of the solutions I found did the trick.

I have some columns in my table that use measures and I want to sum only the rows that contains information.

As we can see in the example below, the total is incorrect.

The measure is defined by the following formula:

CALCULATE(
	[dmdDiasDisponiveisLast30d] * SUM(fPlanogram_analysis[booleanRompido]) * AVERAGE(fVendas_geral[PrecoVendaUnit_100])
)

Note that [dmdDiasDisponiveisLast30d] is also a measure.

I tried using SUMX in many ways but it did not work.

Maybe it's because I'm using another measure inside the calculate...

Do I have to use summarize?

Thanks is advance

[v-zhangti · Accepted solution]

Hi, @revmac 

You can try the following methods.

Measure 2 = CALCULATE([Measure1]*SUM('Table'[Value2])*AVERAGE('Table'[Value3]))

Measure 3 = IF(HASONEVALUE('Table'[Value2]),[Measure 2],SUMX('Table',[Measure 2]))

IF(HASONEVALUE()) can control the output of Total.

Solved: wrong total of measure - Microsoft Power BI Community

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[revmac]

Hi, thanks for the help

I tried your suggestion but unfortunately it dit not work yet.

MEASURE 2

valorVendaPerdidaDMD30d =
CALCULATE(
[dmdDiasDisponiveisLast30d] * SUM(fPlanogram_analysis[booleanRompido]) * [mediaPrecoUn_30d]
)

MEASURE 3
valorVendaPerdidaDMD30d_sumx = IF(HASONEVALUE(fPlanogram_analysis[booleanRompido]),[valorVendaPerdidaDMD30d],SUMX(fPlanogram_analysis,[valorVendaPerdidaDMD30d]))

When I inserted this last measure in the table columns I got the error "Can't display the visual"...

Is it related to the measures inside the measure2? 

Or the relations between columns in the table?

Thanks in advance.

[DimaMD]

@revmac hi, Provide dummy data for testing.

[revmac]

Hi @DimaMD , could you please advise me on how to do that ? I have lots of tables in my pbix... 

[DimaMD]

@revmac  hi, try it 

sumx(
values(table[colun_id]),
[measure])

or
if (
hasonevalue(table[colum]),
[measure],
sumx(value(table[colum])),
[measure])
)