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
Anonymous
Not applicable

How to group by item and day, which still able to breakdown to other category in filter interaction.

Hi Guys,

 

I would like to seek for help on issue below:

My dataset is breakdown to daily, PIC, Stores and Item of Qty Sold. 

 

DatePIC IDStore IDItemQty Sold
1-Jan7001AA01Apple Pie75
2-Jan7001AA01Apple Pie64
3-Jan7001AA01Apple Pie10
4-Jan7001AA01Apple Pie46
1-Jan7001AA02Apple Pie28
2-Jan7001AA02Apple Pie17
3-Jan7001AA02Apple Pie92
4-Jan7001AA02Apple Pie61
5-Jan7001AA02Apple Pie61
17-Jan7002EA03Apple Pie93
18-Jan7002EA03Apple Pie19
19-Jan7002EA03Apple Pie79
20-Jan7002EA03Apple Pie67
1-Jan7001AA01Banana Cake92
3-Jan7001AA01Banana Cake91
1-Jan7001AA02Banana Cake49
2-Jan7001AA02Banana Cake27
3-Jan7001AA02Banana Cake57
4-Jan7001AA02Banana Cake71
5-Jan7001AA02Banana Cake25
31-Jan7002EA03Banana Cake29

 

Currently the requirement is to get the average of qty sold by Item per day. For example, on 1st Jan, Apple Pie is sold in 2 stores, however calculate by item per day is should count as 1 day. Therefore average of qty sold by Apple Pie per day is Total Qty Sold of Apple Pie divided by 9 days (1st Jan-5th Jan & 17th Jan-20th Jan).

In Power Query Editor > "Group by" table for column Item and Date and count Sum of Qty Sold, I am able to get the calculation i wanted by using AVERAGE in Dax in Summarize Table. However my Report have Filters to select Store and PIC. Since Summarize Table no longer breakdown to Store and PIC mean the table relationship is unable to detect and the filter interaction does not works.

Kindly provide some ideas or workaround for this issue.
Thank you. I am greatly appreciate on your help.

 

Regards,
Yit_Lim

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

Hi @Anonymous ,

Based on your descrition, you can create this measure:

Average =
VAR s1 =
    CALCULATE (
        SUM ( 'Table'[Qty Sold] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Item] IN DISTINCT ( 'Table'[Item] ) )
    )
VAR s2 =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Date] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Item] IN DISTINCT ( 'Table'[Item] ) )
    )
RETURN
    s1 / s2

Show it in the card visual and use the Item slicer to filter it:

average.png

Attached a sample file in the below, hopes to help you.

 

Best Regards,
Yingjie Li

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

 

View solution in original post

2 REPLIES 2
v-yingjl
Community Support
Community Support

Hi @Anonymous ,

Based on your descrition, you can create this measure:

Average =
VAR s1 =
    CALCULATE (
        SUM ( 'Table'[Qty Sold] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Item] IN DISTINCT ( 'Table'[Item] ) )
    )
VAR s2 =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Date] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Item] IN DISTINCT ( 'Table'[Item] ) )
    )
RETURN
    s1 / s2

Show it in the card visual and use the Item slicer to filter it:

average.png

Attached a sample file in the below, hopes to help you.

 

Best Regards,
Yingjie Li

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

 

rcharara
Regular Visitor

Did you try to use a Matrix

Where you can have the Date as the first Items in Rows, followed by Item, and PIC ID and you can have the Store ID in Columns and choose Average of Qty Sold for Values.

Group by Item and Day.png

 

Group by Item and Day 2.png

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.