How do i use something like measure inside group by for new measure *BOnPOC HA Mu/\/\uOH

[Anonymous]

Hello dear experts! I hope this question won`t blow your mind))

I `ve been googling  for ages and now i need your help!

In my case I have TableX which contains 2 levels of granularity facts 

first level is LEVEL1, second is LEVEL2. First level exists for both Position and userId, Second level of granularity exists only for Position . I need to calculate average daily LEVEL2Fact and use it on the lower level of granularity but i Fail..Hope you can understand  my Russian English and help me solve my problem.

I have some Measure_of_goodwill = CALCULATE(
SUMX(
GROUPBY

('TableX', [Position], TableX[UserId], TableX[SiteId],
"Fact_", SUMX(CURRENTGROUP(), TableX[LEVEL1Fact]),
"Plan_", SUMX(CURRENTGROUP(), TableX[LEVEL1Plan]),
"LEVEL2_AVERAGE" , SUMX(CURRENTGROUP() , TableX[MeasureAverageOfLevel2])

)

,divide([Fact_], [Plan_] ) - [LEVEL2_AVERAGE] )

,'TableX[UNIONALLTABLEIDENTIFIER] = "LEVEL1")

err msg:
Calculate can not be used in groupby()

The reason it pops up is that i want my average level 2 fact measure [MeasureAverageOfLevel2] to be used inside this group by  temporary table.

 [MeasureAverageOfLevel2]=AVERAGEX

(

SUMMARIZE(TableX,TableX[day] ),

CALCULATE ( SUM ( TableX[LEVEL2Fact] ),TableX[UNIONALLTABLEIDENTIFIER] = "LEVEL2")

)

[Anonymous]

UPDATE!

My buddy created the following example of the same question 

Accessible under the following link:

GOOGLE DRIVE with preview availiable! 

 "Sum the result of a dynamic matrix calculation (grouped by a field)"

The excel file  is provided as a calculation example with a desired final result.

I have a source table that is used to build a matrix.
First of all you chose a period (e.g. March 1st - March 3rd)

In this matrix taxes (when you filter "Sales" as an action) are calculated (a ratio grouped by shop and barista), and then the taxes are summed by shop (so, basically you are summing a bunch of ratios).

Parallel to that, when you filter "Cost" as an action, the daily average cost is calculated (grouped only by shop, no barista).

In the end, for a slice period, you have two numbers for one shop (the tax sum and the average cost), subtract cost from tax (if less than zero then 0), and you get a result which can later be used to calculate a sum for countries, regions etc (region consists of countries, countries consist of shops).

The problem is that when I create a measure the matrix keeps treating all aggregation levels as a shop, in other words, it doesn't calculate a sum of shops, instead it performs all calculation for  a country as it is one big shop.

Any suggestions on how to correctly construct this measure would be appreciated.

Thanks!

[Vera_33 · Accepted solution]

Hi @Anonymous 

It's a ISINSCOPE question, did not have Region level so it is in Total for now, and also no calculation in Country level based on your sample

sumTax = 
SWITCH(TRUE(),
ISINSCOPE(source[Barista_ID]),DIVIDE([sumMoney],[sumQuantity],0),
ISINSCOPE(source[Shop_ID]),SUMX(VALUES(source[Barista_ID]),DIVIDE([sumMoney],[sumQuantity],0)))

Diff = 
VAR V1 = [sumTax] - [avgNet]
RETURN
IF(V1<0,0,V1)

sumDiff = 
SWITCH(TRUE(),
ISINSCOPE(source[Shop_ID]), [Diff],
SUMX(VALUES(source[Shop_ID]),[Diff]))

[Vera_33]

Hi @Anonymous 

It's better to provide some sample data (in a format people can copy) with expected result. Simply modify the measure, not sure if it is working as I don't fully understand your measures without enough context...

Measure_of_goodwill =
VAR T1 =
    GENERATE (
        GROUPBY ( 'TableX', [Position], TableX[UserId], TableX[SiteId] ),
        VAR Fact_ =
            CALCULATE ( SUM ( TableX[LEVEL1Fact] ) )
        VAR Plan_ =
            CALCULATE ( SUM ( TableX[LEVEL1Plan] ) )
        VAR LEVEL2_AVERAGE = [MeasureAverageOfLevel2]
        VAR new =
            DIVIDE ( Fact_, Plan_ ) - LEVEL2_AVERAGE
        RETURN
            ROW ( "new", new )
    )
RETURN
    CALCULATE ( SUMX ( T1, [new] ), 'TableX'[UNIONALLTABLEIDENTIFIER] = "LEVEL1" )

[Anonymous]

@Vera_33  Thank you, I`ll try this as soon as i`ll get my hands on PC with Pbi Desktop installed.