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...
Need Help
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.

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    10 years ago

    Rahul_Bhatt

     

    A follow up. I've found another dynamic way that can cover Year,Quarter,Month level, however it won't work in day level.


    The test pbix is attached for your reference. Please follow below steps.

     

    1. The dataset is as
    2. Add the column [MONTH] in 'Table'.
    3. Add the table 'SummarizedTable'.
    4. Add the column [MonthlyAVG] in 'SummarizedTable'
    5. Create a Many to 1 relationship between 'Table' and 'SummarizedTable'.
    6. Show in the visual

    More details about the steps are in the attached pbix. If you have any question, feel free to let me know.

     

    • Rahul_Bhatt's avatar
      Rahul_Bhatt
      Advocate I
      10 years ago

      Eric_Zhang  i am still facing the issue Please find the data model  and test it on the secnario where for some years data starts 8 and like that .please download the test data.PIBX from below link.

       

      https://we.tl/0tRRy4OtdT

      • Eric_Zhang's avatar
        Eric_Zhang
        Microsoft Employee
        10 years ago

        Rahul_Bhatt

         

        The peak&valley and caused by the absent months. I've created a calender table to fill up the missing months, please check the attachment. By the way, I was testing on the latest May's release, please upgrade your Power Bi desktop if you can't open the pbix.



        Please accept it as solution if the the attaced pbix works, thanks. :) If you have any question, feel free to let me know.