Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
i have the simple scnerio as below .
dw_id diagname dead date
1 heartfailure 2 06/07/2019
2 kidney problem 3 04/07/2019
3 asthama 4 05/07/19
i have like 100 category of dignosisname and i have used the bar chart to show only 10 deads during that period . where i have the date as slicer and dw_id in the filterpane . i need to have the measure of dead % when i categorize by diganosisname = dead of that category/total dead . But when i strict the top 10 it is stricting the total count for that tile and % is showing the wrong value . For example i have total dead for the time period of jan 2019 is 400 and top 10 dead is limiting to 300 count . Value is being calculated on 300 instead of 400 . Is there any way i can ignore the top N filter for calculation ?
currently i am using measure like below :
dead % by diagnname = divide(sum(dead),calculate(sum(head),allexpect(date,dw_id))
Hi @Anonymous
Modified measure "total" as below
total = CALCULATE(SUM(Sheet6[dead]),ALLSELECTED(Sheet6))
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-juanli-msft : To explain further . Here are the two scenerios
1) one with filter : In below way 100% is correct .
2) without filter : But this case is failing like below in the inital reply suggested . I am seeing asthama 40.74 % which is wrong . can i get 11/36 =30.5 % . I know that you suggested to use ALL instead of ALLselected but in this case case 1) is failing . I need both the cases to work . Can i have the formula written in such a way ?
Hi @Anonymous
Based on the measures above
total = CALCULATE(SUM(Sheet6[dead]),ALL(Sheet6)) per_cate = CALCULATE(SUM(Sheet6[dead]),FILTER(ALLSELECTED(Sheet6),Sheet6[diagname]=MAX(Sheet6[diagname]))) % = [per_cate]/[total] rank = RANKX(ALLSELECTED(Sheet6),Sheet6[%],,DESC,Dense) flag = IF([rank]<=SELECTEDVALUE('topn table'[topn]),1,0)
Plus the steps below:
1.Create a table
diagname table = VALUES(Sheet6[diagname])
Leave it alone, don't connect to other tables
add [diagname] in the slicer
2. create measures in Sheet4 (my table)
total_2 = CALCULATE(SUM(Sheet6[dead]),ALLSELECTED(Sheet6)) filtered_name = SELECTEDVALUE('diagname table'[diagname]) %_2 = IF([filtered_name]=MAX(Sheet6[diagname]),[per_cate]/[total_2]) MODIFIED_% = IF(ISFILTERED('diagname table'[diagname]),[%_2],[%])
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-juanli-msft : If you see below screenshot when diagname filtered by asthama i was able to see the chart but when i apply both asthama and hearfailure i am not seeing any data in the chart . Can you help here please :
Hi @Anonymous
1.Create a new table not related to any other table( have no relationship between this table and other tables)
Add [topn] from this table to a slicer
2. create measures in main table
total = CALCULATE(SUM(Sheet6[dead]),ALLSELECTED(Sheet6)) per_cate = CALCULATE(SUM(Sheet6[dead]),FILTER(ALLSELECTED(Sheet6),Sheet6[diagname]=MAX(Sheet6[diagname]))) % = [per_cate]/[total] rank = RANKX(ALLSELECTED(Sheet6),Sheet6[%],,DESC,Dense) flag = IF([rank]<=SELECTEDVALUE('topn table'[topn]),1,0)
3. add [flag] to the visual level filter of the column chart
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hi
Thanks for responding you almost reached my requirement but is not exactly what i am looking for .
to explain that in the screenshot below i have total dead is 36 and as per below i selected top 3 per say . My user is saying he need the formula in below way % for asthama should be = 11/36 =30.5 % but what you are showing is 40.74 .
Reason is total dead is being restricted to top 3 even if we have 5 ranks . But i need the percentage to be calculated in above way . Is that possible if i apply flag as you shown ?
@v-juanli-msft : Thanks for response but still my user request is not solved . Below is the screeshot which i am attaching with clear requirement . if i applied the filter % should be 100 % and without filter it is giving correct value .Below description explains the clearly of requirement:
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
76 | |
74 | |
56 | |
45 |
User | Count |
---|---|
117 | |
105 | |
77 | |
66 | |
64 |