Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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
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
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)
P.S. main issue i need calculate percentage of subtotal from row total, not from grand total
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
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...
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
thank you so much !!! it worked perfectly !!!!
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |