Summarize by average + filtering on a line chart

[Anonymous]

I have a data set with the number of registered users on a website, for each day, detailed between male and female users.

I want to display a line chart showing the evolution of the number of users over time, with drill-down enabled by year+month+day, and the possibility to filter by gender with a slicer. Year and month levels should display an average of the daily values over the period.

If I summarize the value by sum, the daily number of users is correct (male + female), but the monthly and yearly numbers are wrong, because Power BI adds up all the daily values.

If I summarize the value by average, the values are always too low, because it is treating male and female values as distinct values and computes the average between them.

Can you think of a solution to this problem using Power BI ?

I have already tried various solutions using DAX, but I couldn't find a solution myself.

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

"Year and month levels should display an average of the daily values over the period."

Here's my original data table:

 Try this measure:

Measure = 
VAR x = 
CALCULATE(
    COUNT(Sheet12[ registered users]),
    ALLSELECTED(Sheet12[Date])
)
VAR y = 
CALCULATE(
    DISTINCTCOUNT(Sheet12[Date]),
   ALLSELECTED(Sheet12[Date])
)
RETURN
DIVIDE(
    x,y
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

It is exactly what I needed.

With the small variant that I don't have an individual line for each user, so the COUNT was actually a SUM.

But it can be filtered, and works perfectly for all drill-down levels.

Thanks a lot.

[amitchandak]

Can you share your current formula, some sample data, current and expected values