How to create a mean of distinc values in a gauge chart ?

[Anonymous]

Hi Community

I'm not so used to use power BI and maybe you can help me with this problem.

I have a chart dysplaying week by week the number of distinct days where events have taken place. If I have, for week #3, 5 events on Monday, and 10 events on Wenesday, I will have 2 distinct days as result (don't mind the different colors in the chart below). 

This chart is depending on different filters that we can apply (a lot of them, as client, place, type of event, weekend or not, etc....)

I would like to have a second chart (a gauge), related to the first chart and its filters, calcultating the mean of distinct days per week where events have taken place.

Somebody able to help ? 🙂

Thanks a lot 

[Greg_Deckler · Accepted solution]

You would need to create a measure that essentially does a SUMMARIZE of your table by week with a count of days where events have taken place. You would then do an AVERAGEX against that table. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

@Greg_Deckler 

Hi Greg, you're maybe able to help me a 2nd time about the same topic...

Here is my formula from your advice and it works : 

Weekly average = AVERAGEX(SUMMARIZE(ZTM01;ZTM01[Week Number];"Count_of_dinstinct_value";DISTINCTCOUNT(ZTM01[Weekday Number]));[Count_of_dinstinct_value])

The result is the weekly average of the number of distinct weekdays. In my report, the result of this measure depends on filters I apply (the years, the weeks, the clients, the locations....), and it is exaclty what I need. 

With those filters I can for example calculate the average from week #1 to #13 (first quarter) (see picture).

I would like now to calculate those values for each quarter in order to draw a chart representing the year(s), for which I can apply the same filters of my report (years, clients, locations...). Actually I want the same thing than before, but I would like to apply a filter IN the formula. I have tried many solutions such as the one below but without any succcess.

Quarter 1 average = AVERAGEX(SUMMARIZE(FILTER(ZTM01;EARLIER(ZTM01[Week Number])<14);ZTM01[Week Number];"Count_of_dinstinct_value";DISTINCTCOUNT(ZTM01[Weekday Number]));[Count_of_dinstinct_value])

Do you have any idea how to do it ?

Thanks a lot for your help 

[Anonymous]

Hi Greg, 

Thanks for the advice, and thanks for the solution ! It works 🙂 !