Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kstudsrud
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

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

AI.PNG

 

 

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
If this post helps, please consider accept as solution to help other members find it more quickly.

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,

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
If this post helps, please consider accept as solution to help other members find it more quickly.

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?

kstudsrud
Frequent Visitor

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

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.