Forum Discussion

Rahul_Bhatt's avatar
Rahul_Bhatt
Advocate I
10 years ago

Average Per Cover Depends for Dynamic Time Period

My query is  related to Restaurant Point of view, we are looking for a solution which gives me  “Average Per cover straight line” should come on graph  depends on time period like if  we look it  for years then total covers divide by Number of years , quarter, month ,days and so on.  E.g. if covers Sum 1000 and total years are 4 then 1000/4 in same manner if we go for quarter then 1000/4 if we again drill down and go for month then 1000/12 and so on.   

  

. 

Need Help

8 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    10 years ago

    Rahul_Bhatt

    You requirement is not clear for me, what visual would you like to use? Based on my understanding, to get the averages of Year,Quarter or Month, you can follow below steps.

    1. Create a calender table and build up the relationship.

      Date = 
ADDCOLUMNS (
CALENDAR ( MIN('Table'[DT]), MAX('Table'[DT]) ),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" )
)

    2. Create individual measures and use a Multi-row card.
      YearCount = CALCULATE (
    DISTINCTCOUNT( 'Date'[Year]), 
    'Table' 
)

YearAVG = SUM('Table'[QTY])/
    CALCULATE (
    DISTINCTCOUNT( 'Date'[Year]), 
    'Table' 
)

QuarterCount = CALCULATE (
    DISTINCTCOUNT( 'Date'[Quarter]), 
    'Table' 
)

QuarterAVG = SUM('Table'[QTY])/
CALCULATE (
    DISTINCTCOUNT( 'Date'[Quarter]), 
    'Table' 
)

MonthCount = CALCULATE (
    DISTINCTCOUNT( 'Date'[Monthnumber]), 
    'Table' 
)

MonthAVG = SUM('Table'[QTY])/
CALCULATE (
    DISTINCTCOUNT( 'Date'[Monthnumber]), 
    'Table' 
)






    If you have any question, feel free to let me know.

    • Rahul_Bhatt's avatar
      Rahul_Bhatt
      Advocate I
      10 years ago

      Eric_Zhang Thanks for your reply , I am using the "Line & cluster chart" and if you see in below image covers line going up and down as per month i want a straight line which passes thru and shows me this is an average of month for complete year.

       

      • Eric_Zhang's avatar
        Eric_Zhang
        Microsoft Employee
        10 years ago

        Rahul_Bhatt

        A strike-through straight line can be easy in a certain dimension, but so far I have no idea on creating a measure that would vary when drilling down to Year,Quarter,month or day(I doubt it possible?), that's why I used a multi-row card instead.

        For example, an 12 months‘ average straight line passes through months.

        MonthAVG = CALCULATE(SUM('Table'[QTY]),ALL(Table))/
CALCULATE (
    DISTINCTCOUNT( 'Date'[Monthnumber]), 
    'Table' 
)

         

        If you have any question, feel free to let me know.