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
Anonymous
Not applicable

ignore the top N filter for calculation of percentage value

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

 

 

8 REPLIES 8
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

Modified measure "total" as below

total = CALCULATE(SUM(Sheet6[dead]),ALLSELECTED(Sheet6))

12.png

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.

Anonymous
Not applicable

@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],[%])

7.png8.png

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.

Anonymous
Not applicable

@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 :

 

userquestion.jpg

 

 

no data.jpg

v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

1.Create a new table not related to any other table( have no relationship between this table and other tables)

4.png

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

5.png

 

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.

Anonymous
Not applicable

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 ?

 

top10n.jpg

Hi @Anonymous 

Modify formula

total = CALCULATE(SUM(Sheet6[dead]),ALL(Sheet6))

1.png

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.

Anonymous
Not applicable

@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:

%fix.jpg

 

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.