cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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?)

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

1 ACCEPTED SOLUTION
Super User

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]
)``````

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

2 REPLIES 2
Helper II

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

Super User

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]
)``````

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors