Percentage of subtotal

[Anonymous]

I'm trying to get the Matrix table to calculate percentages based on a subtotal.   In this case, I'd like the highlight below to calculate 4 divided by 19 for a total of 21.05%.   Instead, it is looking at the Grant Total and returning a much lower answer (13%).   I've tried multple things but have been unsuccessful.    Please help.

[mattbrice]

Can you try:

%CT Unique =
VAR this_Unique =
    SUM ( Table[Unique] )
VAR group_unique =
    CALCULATE (
        SUM ( Table[Unique] ),
        ALL ( Table[Option Code], Table[Option Description] )
    )
RETURN
    DIVIDE ( this_unique, group_unique )

[Anonymous]

Per my last post minutes ago, I tried recreating the measure as a "new column".   I am still getting the same results.   The line I have highlighted should be around 21%.

 

[v-ljerr-msft]

Hi @Anonymous,

Based on my with your shared pbix file, the formula works fine in your scenario if you use it to create a measure instead of a calculate column. 

Measure %CT Unique = 
VAR this_Unique =
    SUM ( 'options selected'[Unique] )
VAR group_unique =
    CALCULATE (
        SUM ( 'options selected'[Unique] ),
        ALL ( 'options selected'[Option Code], 'options selected'[Option Description] )
    )
RETURN
    DIVIDE ( this_unique, group_unique )

Here is the modified pbix file for your reference. 

Regards

[Anonymous]

This looks awesome!   Works great.   I am interested in how you got the % to appear as a 2-digit decimal (21.05%).   I can't find where I have the same formatting options with a new measure; as a result, my # appears as .21.

Thanks again for your help!

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

Great to hear it works!

You can change the formatting of the measure to Percentage under Modeling tab like below. 

 

Regards

[Anonymous]

This worked out great - Thanks for your help!

[v-ljerr-msft]

Hi @Anonymous,

Could you share a sample pbix file which can reproduce the issue, so that we can further assist on this issue? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[Anonymous]

https://cloud.box.com/s/ykl3vsgdkkudbzdcrrkvldqdzmeegv3l

[v-ljerr-msft]

Hi @Anonymous,

The file is not accessible for me, as I don't have the BOX account. Could you upload it to OneDrive or Dropbox instead and post the link here? 

Regards

[Anonymous]

Here is the file in DropBox:

https://www.dropbox.com/s/019124r18obmf3y/Options%20Selected%20POC3.pbix?dl=0

[Anonymous]

Quick question -- can the "unique" field which I created as a new measure be included in the formula?   It doesn't appear to be recognized as a Table field like the other fields when I try to use this example .

[Anonymous]

What are are the proper steps for me to share this file with this group?   Do I need to publish and provide the URL?  Please advise.

[Anonymous]

My organization has turned off the feature to share files with anyone outside the organization.    Are there any other suggestions?   I'd really like to understand how I can calculate based on the sub total vs. the grand total.    I am trying to use the information provided earlier in this post, however, I believe the solution provided is intended for calculating and summing $$.   I am working with whole #'s, not dollar amounts.

[v-ljerr-msft]

Hi @Anonymous,

Have you tried the solution provided above? Does it work in your scenario? If it works, could you accept it as solution to close this thread?

If you still have any question on this issue, feel free to post here. 

Regards

[Anonymous]

I have tried the solution provided but was not as successful.   I will try to share out.   Currently I am unable to share from my personal drive to others outside the company.  

[tringuyenminh92]

Hi @Anonymous,

I think your concern looks like this question , so the idea is using ALLSELECTED subtotal, you could refer my topic Percent of subtotal/groups in Power BI for details.

If this works for you please accept it as solution and also like to give KUDOS.

Best regards
Tri Nguyen

[CheenuSing]

HI @Anonymous

Can you share the pbix file in the One Drive and share the link to check out.

Cheers

CheenuSing