cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper III

## Need Help to calculate Row Subtotals in Matrix where I am using measure to show values for each row

Hi All,

I am using matrix to get the hierarchy level view and their values accordingly.
In below screenshot, I have written measure using switch statement which are mentioned below.
I am struggling to get the correct values in rows subtotal , here's an example Pending = [Open]+[Non-Open]+[Non-Completion] which I am unable to achieve.

Request you all to please help me to resolve this issue. (Enabling Row subtotals in matrix is giving incorrect values which is 62 in screenshot)

Measure =
SWITCH(MAX('Table'[Level1]),"Pending",SWITCH(MAX('Table'[Level2]),
"Open",[Open],
"Non-Open",[Non-Open],
"Non-Completion",[Non-Completion])
BLANK())

I would greatly appreciate if you could help and response.

2 ACCEPTED SOLUTIONS
Super User

Please check the below picture and the link down below whether it is what you are looking for.

all measures are in the sample pbix file.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Super User

Hi,

I am not sure, but I don't think there is logic. I think the result does not depend on the hierarchy.

Please also share the number that you want to see.

Do you want to see 290% for Total Records Pushed?

Thanks.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

10 REPLIES 10
Super User

If it is OK with you, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

Thanks.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Helper III

Hello @Jihwan_Kim , Thanks a lot for your comment.

Here's the link to sample pbix file :

Please let me know if you face any issues to open the file.
Please note that this is just sample file created by me where I have just hardcoded the measure values.
As I said earlier, I have got stuck to calculate subtotal rows with correct values.

Super User

I am not sure if this suits your case, but please check the below picture and the link down below.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Helper III

Hello @Jihwan_Kim ,

Please let me know if you get the time to look into this. I have been trying but was not able to remove the blank rows from matrix neither the rows subtotal of KPI%

Please guide me if such requirement can be achieved with different approach.

Also, for KPI% the logic for rows subtotal(parent level is not always 100% ) is different .
For example,Email Submission= 290
Open = 50, Non-Open=60, Non-completion =70
then KPI% of Pending Email Survey = ([Open]+[Non-Open]+[Non-completion])/[Email Submission]*100 = 180/290=62%
Similarly,KPI% of Email Invitation Sent=110/290=37.9%

Please let me know if this can be achieved or any alternative approach to meet the requirement.

@amitchandak @Greg_Deckler @parry2k @MFelix @Ashish_Mathur

Super User

Please check the below picture and the link down below whether it is what you are looking for.

all measures are in the sample pbix file.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Helper III

Hello @Jihwan_Kim , Thank you for your quick response.

The above doensn't exactly the same what I am looking for. here's the pattern for the KPI % logic

2.Total record Scrubbed out =Total record scrubbed out/Total record received
2a.Black Records=Black record/Total record Scrubbed out
2b.Duplicate Records=Duplicate record/Total record Scrubbed out
2c.Invalid Records=Invalid record/Total record Scrubbed out
3.Total Record Pushed= Total Record Pushed/Total record received
3a. Email Submission= Email Submission Statistics/Total Record Pushed
3b.Pending Email Survey=Pending Email Survey/Total Record Pushed
i.Open= Open/Pending Email Survey
ii.Non-Open= Non-Open/Pending Email Survey
iii.Non-Completion=Non-completion/Pending Email Survey
4.Email pending= Email Pending/Total record received

KPI Name : It always divide by Total record received
Other level1,2,3,4 etc divide by their preceding parent level

For example : Pending Email Survey is child of Total Record Pushed so it divides by Total Record Pushed.
Please check this hope this is clear now. I am also trying meanwhile 😞

Thanks a lot

Super User

Hi,

I am not sure, but I don't think there is logic. I think the result does not depend on the hierarchy.

Please also share the number that you want to see.

Do you want to see 290% for Total Records Pushed?

Thanks.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Helper III

Thats amazing, you are Brilliant @Jihwan_Kim  !!!!!!
This is what I was looking for. Thank you veryyyyyy muchhhhhh .
I know there is no logic but this is what I have got the reuirement and the numbers were just dummy so its fine. I will apply this in original file.

Thanks a lot again !  Really Appreciate for all your time and helping me to meet the requirement.

Helper III

Dear @Jihwan_Kim ,
First of all I would really appreciate and very thankful to you for taking time out of your busy schedule to read this and looking into this issue to resolve this.

I am glad to say that 80% of my problem is fixed now. KPI% issue is still there as it has custom calculation at parent level
Let me explain this with example for better understanding

KPI% of Total record received = 100%
KPI% of Total Record Scrubbed Out = DIVIDE(SUM(Total Record Scrubbed Out),SUM(Total record received))
KPI% of Total Record Pushed = DIVIDE(SUM(Total Record Pushed),SUM(Total record received))
KPI% of Email Submission Statistics = DIVIDE(SUM(Total Email Submission),SUM(Total record pushed))
KPI% of Email Invitation Sent = DIVIDE(SUM(Total Email Invitation Sent),SUM(Total Email Submission))
KPI% of Pending Email Survey= DIVIDE(SUM(Total Pending Email Survey),SUM(Total Email Invitation Sent))

Please check this logic and guide me how can we achieve this. I am so sorry to keep asking for your help.
I am still in learning phase of powerbi .

Thanks & Regards,
Sarika

Super User

Sorry that I do not understand your logic. I think perhaps I do not understand your business fully.

Please check the below link that I have fixed, but I am not convinced that I met your requirement.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors