Show subtotals percentage of row on matrix.

[knygneshys]

Hi,

i'm new here. Maybe can anybody help me. I need to show in matrix subtotals perecentage of rows. For now i got result :

There are percentages subtotal of Grand total, i need percentage of subtotal ROW. It's should be like that:

Maybe anybody can help me? 

[Anonymous]

Hi @knygneshys,

Can you please provide the sample data and the formula? It will be help for test and modify your formula.

Regards,

Xiaoxin Sheng

[knygneshys]

Hi, i'm using formula would be:

Centralizuotų pirkimų vertė, proc. = FORMAT(DIVIDE (
CALCULATE ( SUM (viskas[Centralizuotų pirkimų vertė]) );
CALCULATE ( SUM ( viskas[Pirkimų vertė] ); ALLEXCEPT ( viskas; viskas[Pirkim_būd] ))); "0.0%")

and second measure formula:

Žaliųjų pirkimų vertė, proc. = FORMAT(DIVIDE (
CALCULATE ( SUM (viskas[Žaliųjų pirkimų vertė]) );
CALCULATE ( SUM ( viskas[Pirkimų vertė] ); ALLEXCEPT ( viskas; viskas[Pirkim_būd] ))); "0.0%")

the data is very big amount, it would like this:

The main issue, that i need calculate subtotals not from grand total, but from the row total (Tarptautinis / Supaprastintas)

[knygneshys]

P.S. main issue i need calculate percentage of subtotal from row total, not from grand total

[Anonymous]

Hi @knygneshys,

If you add some filters to ignore the original filter in measures, the formula will not suitable on total row(show the wrong result), perhaps you can try to add some conditions to check if current row is the total row, then add the specifty formula which works on total row.

Centralizuotų pirkimų vertė, proc. =
var currTarPtau=LASTNOBLANK(viskas[TarPtauis],[TarPtauis])
var currPirkimo=LASTNOBLANK(viskas[Pirkimo Budas],[Pirkimo Budas])
return
if(COUNTA(viskas[Pirkimo Budas])=COUNTAX(FILTER(ALL(viskas),[TarPtauis]=currTarPtau&&[Pirkimo Budas]=currPirkimo),[Pirkimo Budas]),
		 "Formula of total row",
FORMAT(DIVIDE (
CALCULATE ( SUM (viskas[Centralizuotų pirkimų vertė]) );
CALCULATE ( SUM ( viskas[Pirkimų vertė] ); ALLEXCEPT ( viskas; viskas[Pirkim_būd] ))); "0.0%"))

 

Žaliųjų pirkimų vertė, proc. =
var currTarPtau=LASTNOBLANK(viskas[TarPtauis],[TarPtauis])
var currPirkimo=LASTNOBLANK(viskas[Pirkimo Budas],[Pirkimo Budas])
return
if(COUNTA(viskas[Pirkimo Budas])=COUNTAX(FILTER(ALL(viskas),[TarPtauis]=currTarPtau&&[Pirkimo Budas]=currPirkimo),[Pirkimo Budas]),
		 "Formula of total row",
		 FORMAT(DIVIDE (
CALCULATE ( SUM (viskas[Žaliųjų pirkimų vertė]) );
CALCULATE ( SUM ( viskas[Pirkimų vertė] ); ALLEXCEPT ( viskas; viskas[Pirkim_būd] ))); "0.0%"))

Notice: Replace "Formula of total row".

Regards,

Xiaoxin Sheng

[knygneshys]

Hi, i have made some changes, like you wrote, that's my measure:

Test for subtotal of rows =
var currTarPtau= LASTNONBLANK(viskas[Tarptautinis / Supaprastintas];[Tarptautinis / Supaprastintas])
var currPirkimo= LASTNONBLANK (viskas[Pirkimo būdas];[Pirkimo būdas])
return
if(COUNTA(viskas[Pirkimo būdas])=COUNTAX(FILTER(ALL(viskas);[Tarptautinis / Supaprastintas]=currTarPtau&&[Pirkimo būdas]=currPirkimo);[Pirkimo būdas]);
"Formula of total row";
FORMAT(DIVIDE (
CALCULATE ( SUM (viskas[Centralizuotų pirkimų vertė]) );
CALCULATE ( SUM ( viskas[Pirkimų vertė matoma] ); ALLEXCEPT ( viskas; viskas[Pirkimo būdas] ))); "0.0%"))

I didnt replace "Formula of total row", but i got result:

So subtotals are the same as was, but changed only those place which was good for me... And i need subtotals percentage...

[Anonymous · Accepted solution]

Hi @knygneshys,

Perhaps you can try to remove the second conditions and test again.

Test for subtotal of rows =
var currTarPtau= LASTNONBLANK(viskas[Tarptautinis / Supaprastintas];[Tarptautinis / Supaprastintas])
return
if(COUNTA(viskas[Pirkimo būdas])=COUNTAX(FILTER(ALL(viskas);[Tarptautinis / Supaprastintas]=currTarPtau);[Pirkimo būdas]);
"Formula of total row";
FORMAT(DIVIDE (
CALCULATE ( SUM (viskas[Centralizuotų pirkimų vertė]) );
CALCULATE ( SUM ( viskas[Pirkimų vertė matoma] ); ALLEXCEPT ( viskas; viskas[Pirkimo būdas] ))); "0.0%"))

Since I have test on actual data, can you share some sample data to test?

Notice: below is the screenshot which I used to test filter on total row.

Regards,

Xiaoxin Sheng

[knygneshys]

thank you so much !!! it worked perfectly !!!!