cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kstudsrud Frequent Visitor
Frequent Visitor

Percentages of column values

AI.PNG

 

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
kstudsrud Frequent Visitor
Frequent Visitor

Percentages of column

AI.PNG

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.

 

 
kstudsrud Frequent Visitor
Frequent Visitor

Percentages of column

AI.PNG

 

 

 

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.

 

kstudsrud Frequent Visitor
Frequent Visitor

Percentages of Certain value in a column

ai2.PNG

 

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

kstudsrud Frequent Visitor
Frequent Visitor

Re: Percentages of Certain value in a column

AI.PNG

 

 

kstudsrud Frequent Visitor
Frequent Visitor

Percentages

AI.PNG

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
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: | |
kstudsrud Frequent Visitor
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. 

 

subject.PNG

 

 

 

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

 

site.PNG

 
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...

 

ai column.PNGcontact table.PNG

 


Thanks,

Highlighted
Community Support Team
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: | |

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 60 members 1,300 guests
Please welcome our newest community members: