Multiple group by columns

Question

hi ,I am struggling since 2 weeks,&nbsp;need someone help&nbsp;I have few columns in a table,Dimension: Product, Product area, Product Line, Product Item Id, Working days, Shift ID, Work Id and Product Lovmeasure : SOMMI want multiple group by statement :&nbsp;&nbsp; &nbsp; &nbsp;first :&nbsp;( avergae of SOMM&nbsp; &nbsp; &nbsp; group by Product, Product Line,Working days,Shift ID,&nbsp; &nbsp;Work Id )&nbsp; and then use the result togroup by&nbsp;Product, Product area, Product Line, Product Item Id , Sum(first group by)and want to create a tableProduct Lov, group by overall.&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

anonymous · Answer

Hi Nobie,
Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly&nbsp;&nbsp;
Regards,
Xiaoxin Sheng

nobie · Answer

Hi ,can u plz ping me at&nbsp;alimdfarukh@gmail.com, we can have a call . It will be a great conversation .as this is very strange and unique issues.

anonymous · Answer

HI&nbsp;Nobie,
You can try to use the following dax formula to create a new table with these group by rules:
NewTable =
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        [Product],
        [Product area],
        [Product Line],
        [Product Item Id],
        [Product Lov],
        "AVG SOMM",
            CALCULATE (
                AVERAGE ( Table[SOMM] ),
                VALUES ( Table[Working days] ),
                VALUES ( Table[Shift ID] ),
                VALUES ( Table[Work Id] )
            )
    )
RETURN
    GROUPBY (
        summary,
        [Product Lov],
        "Overall", SUMX ( CURRENTGROUP (), [AVG SOMM] )
    )
Notice: please not attach sensitive data in your reply.
Regards,
Xiaoxin Sheng

nobie · Answer

Thanks but not geting the actual answer.plz feel free to ping on mail =&gt;alimdfarukh@gmail.com

nobie · Answer

hi&nbsp;Anonymous&nbsp;its basically doing is multiple group by&nbsp;first it will group by&nbsp;Product, Product Line,Working days,Shift ID,&nbsp; &nbsp;Work Id and determine the average of&nbsp;SOMM.then it need to group by&nbsp;&nbsp;Product, Product area, Product Line, Product Item Id&nbsp; and do the sum of previous avergae column.at the last i want to draw a matrix visual of&nbsp;product Lov ,sum of last measure.

Forum Discussion

Multiple group by columns

6 Replies

Related content

Power Query - Aggregate multiple columns within group by

Grouping rows by Column and then updating Column

Create columns group

Grouping Columns

Multiple column with group by