cancel
Showing results for
Did you mean:
Frequent Visitor

## Percentages of column values

Hello,

I am working on a dash board that ties Actionable Intelligence (AI) to a patient in dynamics 365. I am currently trying to solve the issue of percentages within a column. I want to find the percentage between Asthma 2017 / Total Patient Population 2017  &  Asthma 2018 / Total Patient Population 2018 then be able to drill down to see the percentage by site (ex. Quality of Life Health Services, Christ Health) . I believe the issue is that in the "Subject" column ("05 Asthma -2017", "05 Asthma -2018",  "Total Patient Population -2017", Total Patient Population -2018") are all in there.

This is the code I used for %ALL2 (which turned out wrong)  :
CALCULATE(
DIVIDE(
CALCULATE(
[SUMAi],
VALUES(AI[xhis_name])),
CALCULATE( [SUMAi], AI[xhis_name] = "Total Patient Population - 2017" || AI[xhis_name] = "Total Patient Population = 2018") ) )

Just started using PowerBI and DAX this summer, any advice would be awesome!
Thanks.

8 REPLIES 8
Frequent Visitor

## Percentages of column

Hello,

I am working on a dash board that ties Actionable Intelligence (AI) to a patient in dynamics 365. I am currently trying to solve the issue of percentages within a column. I want to find the percentage between Asthma 2017 / Total Patient Population 2017  &  Asthma 2018 / Total Patient Population 2018 then be able to drill down to see the percentage by site (ex. Quality of Life Health Services, Christ Health) . I believe the issue is that in the "Subject" column ("05 Asthma -2017", "05 Asthma -2018",  "Total Patient Population -2017", Total Patient Population -2018") are all in there.

This is the code I used for %ALL2 (which turned out wrong)  :
CALCULATE(
DIVIDE(
CALCULATE(
[SUMAi],
VALUES(AI[xhis_name])),
CALCULATE( [SUMAi], AI[xhis_name] = "Total Patient Population - 2017" || AI[xhis_name] = "Total Patient Population = 2018") ) )

Just started using PowerBI and DAX this summer, any advice would be awesome!
Thanks.

Frequent Visitor

## Percentages of column

Hello,

I am working on a dash board that ties Actionable Intelligence (AI) to a patient in dynamics 365. I am currently trying to solve the issue of percentages within a column. I want to find the percentage between Asthma 2017 / Total Patient Population 2017  &  Asthma 2018 / Total Patient Population 2018 then be able to drill down to see the percentage by site (ex. Quality of Life Health Services, Christ Health) . I believe the issue is that in the "Subject" column ("05 Asthma -2017", "05 Asthma -2018",  "Total Patient Population -2017", Total Patient Population -2018") are all in there.

This is the code I used for %ALL2 (which turned out wrong)  :
CALCULATE(
DIVIDE(
CALCULATE(
[SUMAi],
VALUES(AI[xhis_name])),
CALCULATE( [SUMAi], AI[xhis_name] = "Total Patient Population - 2017" || AI[xhis_name] = "Total Patient Population = 2018") ) )

Just started using PowerBI and DAX this summer, any advice would be awesome!
Thanks.

Frequent Visitor

## Percentages of Certain value in a column

I am trying to find the percentage of Asthma 2017 to total population 2017 & percentage of Asthma 2018 to total population 2018 within this column while still being able to filter by other column in table. I have used COUNTROWS and also created a dummy column with "1" and used SUM. Any suggestions would be great

Frequent Visitor

Frequent Visitor

## Percentages

I am trying to find the percentage of Asthma of total population in this model for 2017 & 2018. The problem is that I do not get the right values for 2018.

The code have for %ALL2 is...
%ALL2 =
CALCULATE(
DIVIDE(
CALCULATE( [SUMAi],
VALUES(AI[xhis_name])),
CALCULATE( [SUMAi], AI[xhis_name] = "Total Patient Population - 2017" || AI[xhis_name] = "Total Patient Population = 2018")))

Any suggestions?

Community Support Team

## Re: Percentages of Certain value in a column

Hi @kstudsrud,

Since I'm not so clear which measure([SUMAi]) formula you calculate, can you please share more detailed information and same data to help us clarify your scenario and test?

BTW, measures who has specific filter not fully works on need calculation. It will calculate drill to detail records to calculate and summarised instead of calculated on summarised records.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
Frequent Visitor

## Re: Percentages of Certain value in a column

SUMAi = SUM(AI[Count] )

AI[Count] = I gave each row in the table a value of 1 for each pateint instead of using COUNTROWS

Below shows the  column where i am trying to find the percentage of Asthma by total population for both 2017 & 2018.

Below is a related table that I want to filter by site for the number of patients with asthma and also the percentage compared to the total population in both year 2017 & 2018

Here is the formula I used but got the right values for 2017 but not 2018

%ALL2 =
CALCULATE(
DIVIDE(
CALCULATE( [SUMAi],
VALUES(AI[Subject])),
CALCULATE( [SUMAi],
AI[Subject] = "Total Patient Population - 2017" || AI[Subject] = "Total Patient Population = 2018")))

Here are the two tables i am working with...

Thanks,

Highlighted
Community Support Team

## Re: Percentages of Certain value in a column

HI @kstudsrud,

I think this issue is due to typo, it seems like you type '=' instead '-', it is calculate with wrong conditions.

```%ALL2 =
CALCULATE(
DIVIDE(
CALCULATE( [SUMAi],
VALUES(AI[Subject])),
CALCULATE( [SUMAi],
AI[Subject] = "Total Patient Population - 2017" || AI[Subject] = "Total Patient Population = 2018")))```

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 60 members 1,300 guests
Recent signins: