cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mossshvlw
Helper II
Helper II

Question about Percent of Grand Total - Unpivoted Data

Hi all,

 

I have a dataset of 100 rows, each row is a unique case which can have Drug A through Drug H marked as either 1 (present) or 0 (not present).  Multiple drug types can be found in a single case. 

 

On the chart, I want to display the percentage of each drug type present as a percentage of the total 100 cases.  For this, I'm using a clustered bar chart.

 

In order to make the clustered bar chart, I select the columns Drug A through Drug H, and 'unpivot' the selected columns.  Then I put "attribute" on the Axis, and 'value' on the Values.  

 

I'm able to display the raw counts of each drug, but when I try to "show value as" percent of grand total, it gives the percentage based on the unpivoted data, which is now many more rows than the original 100.  I want them to be % of the total 100 cases.  In this sense, the percentages would not add up to 100%, but rather, Drug A would be be 37% (found in 37 of the 100 cases), Drug B would be 9 % (found in 9 of the 100 cases), etc. etc.

 

Workbook is here (this forum won't let me attach .pbix file here?)

 

https://app.box.com/s/wvkk4fzrxqg1tr25p3k7dlfmpqvuaeg8 

 

so you can see my thought process. Hope my question makes sense. Any help much appreciated!  

1 ACCEPTED SOLUTION
selimovd
Community Champion
Community Champion

Hey @mossshvlw ,

 

I'm not sure if I totally understood the result.

With the following measure you can count the rows with Value 1:

Count Drug =
CALCULATE(
    DISTINCTCOUNT( 'Pivoted Data'[Case ] ),
    'Pivoted Data'[Value] = 1
)

 

Then you need the total amount of rows what seems to be identical to the distinct cases:

Total Amount = DISTINCTCOUNT( 'Original Data'[Case ] )

 

And then you can calculate the percentage:

% GT =
DIVIDE(
    [Count Drug],
    [Total Amount]
)

 

10.png

 

If you want everything in one measure you can also do that:

% GT =
VAR vCountDrug =
    CALCULATE(
        DISTINCTCOUNT( 'Pivoted Data'[Case ] ),
        'Pivoted Data'[Value] = 1
    )
VAR vTotalAmount = DISTINCTCOUNT( 'Original Data'[Case ] )
VAR vResult =
    DIVIDE(
        [Count Drug],
        [Total Amount]
    )
RETURN
    vResult

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

View solution in original post

2 REPLIES 2
mossshvlw
Helper II
Helper II

Ahh, of course !  I should've known I could just do a new measure to get %.  this works perfectly, thanks a lot !

selimovd
Community Champion
Community Champion

Hey @mossshvlw ,

 

I'm not sure if I totally understood the result.

With the following measure you can count the rows with Value 1:

Count Drug =
CALCULATE(
    DISTINCTCOUNT( 'Pivoted Data'[Case ] ),
    'Pivoted Data'[Value] = 1
)

 

Then you need the total amount of rows what seems to be identical to the distinct cases:

Total Amount = DISTINCTCOUNT( 'Original Data'[Case ] )

 

And then you can calculate the percentage:

% GT =
DIVIDE(
    [Count Drug],
    [Total Amount]
)

 

10.png

 

If you want everything in one measure you can also do that:

% GT =
VAR vCountDrug =
    CALCULATE(
        DISTINCTCOUNT( 'Pivoted Data'[Case ] ),
        'Pivoted Data'[Value] = 1
    )
VAR vTotalAmount = DISTINCTCOUNT( 'Original Data'[Case ] )
VAR vResult =
    DIVIDE(
        [Count Drug],
        [Total Amount]
    )
RETURN
    vResult

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors