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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
janinw
Helper I
Helper I

Visual with Top N not working upon using one specific filter - shows same value for each

Hi,

 

I have a visual which shows the top 5 reasons of why customers churn.

 

The value of each reason's share is calculated like so

Share of Tickets by Reason = DIVIDE (DISTINCTCOUNT(table[ticket_id]),CALCULATE (DISTINCTCOUNT(table[ticket_id]), ALL((table[Reason]))))
 
The visual, without any filters set, normally looks as follows
2021-09-16 11_20_49-B.6 - MKT - DE - Reasons for not Scheduling an Appointment - Power BI.png

 

Then, when I use a page filter (by slicer) for week selection

2021-09-16 11_20_18-B.6 - MKT - DE - Reasons for not Scheduling an Appointment - Power BI.png

 

This is what happens

2021-09-16 11_19_36-B.6 - MKT - DE - Reasons for not Scheduling an Appointment - Power BI.png

 

When I use a page filter that for example filters for months, it works all fine, it shows the visual as it should, showing for all the weeks within these months. Only with this week filter I get a flat line for each reason and they all have a share of 100%. (It only happens within this visual, any tables that show the same metric work perfectly fine.)

I am lost - any help is appreciated.

 

Best,
Janine

 
 
1 ACCEPTED SOLUTION
janinw
Helper I
Helper I

HI @MFelix thanks for your answer, with a little help from your answer and some more PowerBI DAX researching (and blunt trying out) I managed to solve the issue by changing the DAX from

ALL((table[Reason])

to 

 

ALLEXCEPT(no_appointment_reasons, no_appointment_reasons[reporting_week])

As I want to calculate the percentage over all the tickets from that week, as it is displayed on a weekly level in the visual.

View solution in original post

2 REPLIES 2
janinw
Helper I
Helper I

HI @MFelix thanks for your answer, with a little help from your answer and some more PowerBI DAX researching (and blunt trying out) I managed to solve the issue by changing the DAX from

ALL((table[Reason])

to 

 

ALLEXCEPT(no_appointment_reasons, no_appointment_reasons[reporting_week])

As I want to calculate the percentage over all the tickets from that week, as it is displayed on a weekly level in the visual.
MFelix
Super User
Super User

Hi @janinw ,

 

You are making the ranking using the ALL statement, further more you do it based on a column that is the column Reason that way the calculation will only be correct when you have all the values in your table select so when you start to make filters based on other columns the table/column/row context changes and your result always returns 1.

 

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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