Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
knygneshys
Frequent Visitor

Show subtotals percentage of row on matrix.

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 :

 

pb1.jpg

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

 

pb2.jpg

 

Maybe anybody can help me? 

1 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.

Capture.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

7 REPLIES 7
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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:

 

pb3.jpg

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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:

pb4.jpg

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.

Capture.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.