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

[SarikaKumari18]

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.
Thanks in advance !!!

[Jihwan_Kim · Accepted solution]

Hi, @SarikaKumari18 

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.

https://www.dropbox.com/s/l7m1o2543li5boq/Matrix-HierarchyLevel%20%281%29.pbix?dl=0 

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.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Jihwan_Kim]

Hi, @SarikaKumari18 

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.

[SarikaKumari18]

Hello @Jihwan_Kim , Thanks a lot for your comment.

Here's the link to sample pbix file : 
https://drive.google.com/file/d/1u-KibZBqJpjnMpDdUzg4gPoLaC6glXEL/view?usp=sharing

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.

Thanks again! Looking forward to hear from you ..Please help

[Jihwan_Kim]

Hi, @SarikaKumari18 

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

https://www.dropbox.com/s/jk6eehkrt1o5dwn/Matrix-HierarchyLevel.pbix?dl=0 

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.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[SarikaKumari18]

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.

Kindly share the related link to refer if you find please.
Thanks in advance 
@amitchandak @Greg_Deckler @parry2k @MFelix @Ashish_Mathur 

[Jihwan_Kim · Accepted solution]

Hi, @SarikaKumari18 

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.

https://www.dropbox.com/s/l7m1o2543li5boq/Matrix-HierarchyLevel%20%281%29.pbix?dl=0 

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.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[SarikaKumari18]

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 

1.Total record recieved = Total record received/Total record received
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

[Jihwan_Kim · Accepted solution]

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. 

Please check the below link. 

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

Thanks.

https://www.dropbox.com/s/l7m1o2543li5boq/Matrix-HierarchyLevel%20%281%29.pbix?dl=0 

[SarikaKumari18]

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.

[SarikaKumari18]

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

[Jihwan_Kim]

Hi, @SarikaKumari18 

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.

https://www.dropbox.com/s/l7m1o2543li5boq/Matrix-HierarchyLevel%20%281%29.pbix?dl=0