Correct Total using ALL() whilst also having blanks filtered out of visual?

[dtd_89]

Hi

I'm hoping someone may have a quick solution to a problem i'm encountering whilst trying to provide % of total in a bar chart.

The screenshot below shows a dummy dataset for customer feedback replicating the issue i'm encountering. The data spans over 3 days (20th Oct to 22 Oct), with a total of 25 customers, 9 of which opted not to give feedback.

I'm needing to present this data as a % broken out by feedback score. I have used the 'feedback%' measure shown below, which leverages the ALL() function to clear the feedback_rating filter, providing a total customer count. You can see from the 2nd bar chart that this works when the blank feedback rating option is included in the visual. Example feedback score of 1 has 5 customer entries, 5/25 = 20%.

I have been asked to remove blanks from the visual. When these are removed however, the % are incorrect as can be seen within the red bar chart, a feedback score of 1 now has a % distribution of 26%.

The reason appears to be due to the Date slicer which is required in my real dashboard. As all the customers on 21st October did not leave feedback, I believe when the blanks are removed from the visual, all of these rows are removed from the underlying data, creating a new customer total of 19, resulting in incorrect %'s, feedback score of 1 now becoming 26% (5/19) as mentioned above.

Any ideas on how to keep these values in the total whilst also keeping the blank entries out of the visual? Ideally I would like to solve it within a DAX measure as my existing dashboard already has a relatively complex data model, however I appreciate it may require a helper table of some sort.

feedback% =

distinctcount(Sheet1[customer_id]) /

CALCULATE(DISTINCTCOUNT(Sheet1[customer_id]),ALL(Sheet1[feedback_rating]))

Thanks for reading. 

[Dangar332]

Hi, @dtd_89 

check your feedback_rating column data type 
change it to whole number

[dtd_89]

Hi, it's already set as a whole number, thanks

[Dangar332]

Hi, @dtd_89 

check below image

[dtd_89]

Thanks for the screenshot, in your underling dummy data, do you have a day were no customers left feedback, i.e. the feedback column is blank?

When there is a day with no feedback scores, when blanks are omitted from the visual, these customers are also lost from the overall count, even though ALL([Feedback_rating]) is being used.

I guess it is to do with the ordering of filtering by BI, however I'm yet to figure out a way around it other than using seperate aggreagated tables which in big datasets becomes complex.

Thanks

[Dangar332]

Hi, @dtd_89 

i am not able to find file that i am attached screenshot above.

but there nothing to do i just drag and drop your column in visuals it automatically omitted blank value and give output like above.

i think try to update your version i am use (sep-23).

it done auto as a show up above.

[sjoerdvn]

try adding an "IF", so the measure returns blank when the feedback rating is blank.

feedback% = IF(CALCULATE(DISTINCTCOUNT(Sheet1[feedback_rating]),Sheet1[feedback_rating])>0,
distinctcount(Sheet1[customer_id]) /
CALCULATE(DISTINCTCOUNT(Sheet1[customer_id]),ALL(Sheet1[feedback_rating]))
)

[dtd_89]

No joy unfortunately, adding in the IF statement produces the same results as the red bar chart I included in my screenshot. Thanks 

[ValtteriN]

Hi,

I tried to recreate this issue with mock data, but couldn't. I recommend you create a test measure to check the denominator value e.g. 

Measure 8 = CALCULATE(DISTINCTCOUNT('Table (12)'[Customer]),ALL('Table (12)'[Answer]))

If the result is 25 like here the issue is else where:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[dtd_89]

Thanks for having a look and the advice.

I'm unable to see all of your data, so can't determine the expected results of the calc. Do you have any dates in your dummy data where no customers have provided feedback? This is what is causing the incorrect Denominator within my data, i.e. the count for these days is being removed from the total.

Thanks