Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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
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
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,
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
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?
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.
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |