Incorrect Subtotal and Grand total value of measure (division)

[xkoby001]

Hello and thank you all, who helped me with other issues (I have never posted here before, but I found so many solutions for my tasks)!

Info:

here is a data model

Pivot table of my report looks like this

Problem:

there are two measures (divide_TEST and total_divide_TEST which contains the first one),

divide_TEST:=DIVIDE(
CALCULATE(
	SUM(DATA[value_bgly]);DATA[index]="Gross revenue");
CALCULATE(
	SUM(DATA[value_bgly]);DATA[index]="Volume (bottles)");
	0)

total_divide_TEST:=IF(
HASONEVALUE(DATA[name_reporting]);
[divide_TEST];
SUMX(SUMMARIZE(
DATA;
DATA[name_reporting];
DATA[sales_area];
"__TOTAL__";[divide_TEST]);[__TOTAL__])
)

but neither one do not works as I need → Subtotal and Grand total are not the sum of rows.

But, when I'll rotate my PT, somethings gonna work partially:

1st measure shows Subtotal and Grand total everywhere, but the wrong one unlike the 2nd measure, which shows Subtotal and Grand total only in "Gross revenue" and "Totals" section. And even only section "Totals" shows the correct evaluation of Subtotal and Grand total of 2nd measure. I've spent a lot of time to solve summarization of division in row line, but I've no deeper knowledges and skills to work with hierarchy, and I think the problem is in it.

Help me please to resolve this task! (file)

J=D

[v-cherch-msft · Accepted solution]

Hi @xkoby001 

You may create below measures to get correct total for measure [divide_TEST] and [total_divide_TEST].You may turn off the column subtotals.

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

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

Measure = SUMX(VALUES(DATA[name_reporting]),[divide_TEST])

Measure 2 = SUMX(VALUES(DATA[name_reporting]),[total_divide_TEST])

Regards,

[xkoby001]

Hiiiiii @v-cherch-msft !!!!!

Thank you so mucchhhhh!