Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Summarize by average + filtering on a line chart

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.

Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    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.

3 Replies

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

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