Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Rahul_Bhatt
Helper I
Helper I

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.   

  

.Table.jpg 

8 REPLIES 8
Eric_Zhang
Employee
Employee

@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" ) )
    233.PNG
    Capture2333.PNG
  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' 
    )
    Capture.PNG





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

@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.

 

12121.jpg

@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' 
)

捕获.PNG

 

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

@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.
Capture.PNG

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

 

  1. The dataset is as
    Capture222.PNG
  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.

 

@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

@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.

Capture.PNG

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

HI @Eric_Zhang this solution doest not work in case when we select a specific year, its giving me solution in case of all the year selected but what if i use slicer and select the year from that , in that its not working.

 

@Eric_Zhang Thanks Eric,Good job .

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.