Tableau to Power bi Fixed LoD

[Nobie]

Hi Friends,

I need a help , not abe to resolve since many days.
i have a expression in Tableau

sum(

     { FIXED [Product], [Product Area],[Product Line],[Product ID]:
       sum( 
               { FIXED [Product],[Product Line],[working Days],[Shift],[WorkID]:  avg([SOMM])  }   )
      }   
    )

for the inner one   sum( { FIXED [Product],[Product Line],[working Days],[Shift],[WorkID]:  avg([SOMM])  }   )
 i tried to use calculate( average(SOMM),
   allexcept( table,"table"Product],"table"[Product Line],"table"working Days],"table"[Shift],"table"[WorkID]))
but the value I am getting is 350 and in tableau its 20000+.
Thankyou for any help.

[v-yanjiang-msft]

Hi @Nobie ,

It's because you only averaged and did not sum in the DAX formula. Here's my solution, create a measure.

Measure =
VAR _T =
    ADDCOLUMNS (
        'Table',
        "avg",
            DIVIDE (
                CALCULATE (
                    AVERAGE ( 'Table'[SOMM] ),
                    ALLEXCEPT (
                        'Table',
                        'Table'[Product],
                        'Table'[Product Line],
                        'Table'[working Days],
                        'Table'[Shift],
                        'Table'[WorkID]
                    )
                ),
                COUNTROWS (
                    FILTER (
                        'Table',
                        'Table'[Product] = EARLIER ( 'Table'[Product] )
                            && 'Table'[Product Line] = EARLIER ( 'Table'[Product Line] )
                            && 'Table'[working Days] = EARLIER ( 'Table'[working Days] )
                            && 'Table'[Shift] = EARLIER ( 'Table'[Shift] )
                            && 'Table'[WorkID] = EARLIER ( 'Table'[WorkID] )
                    )
                )
            )
    )
RETURN
    SUMX ( _T, [avg] )

I create a sample to test, I attatch it below for your reference.

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

Best regards,

Community Support Team_yanjiang

[Nobie]

Hi @v-yanjiang-msft 
thanks for the solution, the solution didn't worked. But it was nice to have some clarity.
for the inner Fixed I have written a code , and this code and ur code gives the same wrong result
CODE:
Var avg= average('Table'[SOM])
calculate(
SUMX(
SUMMARIZE ('Table',
                          'Table'[Product],'Table'[Line],Table[Working Day],Table[Shift],Table[Worker Id],
                        "inner",avg),[inner]))