Multiple group by columns

[Nobie]

hi ,
I am struggling since 2 weeks, need someone help 
I have few columns in a table,
Dimension: Product, Product area, Product Line, Product Item Id, Working days, Shift ID, Work Id and Product Lov
measure : SOMM

I want multiple group by statement : 
     first : ( avergae of SOMM

      group by Product, Product Line,Working days,Shift ID,   Work Id )

  and then use the result to

group by Product, Product area, Product Line, Product Item Id , Sum(first group by)

and want to create a table
Product Lov, group by overall.

[Anonymous]

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  

Regards,

Xiaoxin Sheng

[Nobie]

@Anonymous 
Hi,
I have attached a goggle drive link which consist of two datasets both are sample only with 1000 rows and 10000 rows for better accuracy check.

the tabeau expression to power bi is:
sum( { fixed [Product],[Product Area],[Product Line],[Item ID] :
          sum( 
{ FIXED [Product],[Product Line],[Working days],[Shift id],[Work ID]: avg(somm)    })

         } )
I will be very thankful if you could help me
https://docs.google.com/spreadsheets/d/1WuJzr1OhTY18Vgg5S8d3vn0YMpIwzqSy/edit?usp=sharing&ouid=114539450464449246981&rtpof=true&sd=true

[Nobie]

Hi ,can u plz ping me at 

alimdfarukh@gmail.com, we can have a call . It will be a great conversation .as this is very strange and unique issues.

[Anonymous]

HI @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]

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

[Nobie]

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