Forum Discussion

PVO3's avatar
PVO3
Impactful Individual
4 years ago

Multiple contexts within iterator

Hello all,

 

I would like some help with a measure.

See the screenshot below. I would like that the result of Measure2 would be 143 for every row.

In VAR _Result I already have the result and they should only be summed.

Only problem is that I can't find the right context within _Result. It basicly just does (Measure1 - 6.5 (_Avg) ) ^2

Tried different SUMX variants, but because of the context within the expression, I can't figure it out.

Example PBIX 

 

Can someone please provide some help? Thanks a lot!

 

 

 

 

Measure1 = 
var MinDate = CALCULATE(MIN('Orders'[Date]),ALLSELECTED('Date')) 
Var MaxDate = CALCULATE(MAX('Orders'[Date]),'Orders'[Date] < DATE(YEAR(TODAY()), MONTH(TODAY()), 1))
RETURN

IF(
    ISFILTERED('Date'[YearMonth]),
        CALCULATE(DISTINCTCOUNT('Date'[YearMonth]),   
            FILTER(ALL('Date'[Date]),[Date] <= MaxDate && [Date] >= MinDate)
        )
)

 

 

 

 

 

 

 

Measure2 = 
VAR _MaxR = CALCULATE(MAXX(VALUES('Date'[YearMonth]),[Measure1]),REMOVEFILTERS())
VAR _Avg = DIVIDE(CALCULATE(SUMX(VALUES('Date'[YearMonth]),[Measure1]),REMOVEFILTERS()),_MaxR)
VAR _Result = ([Measure1] - _Avg)  ^2
RETURN

IF(
    [Measure1] < 1,BLANK(),
    _Result 
)

 

 

 

 

Need Help

10 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    PVO3 , try like

     

     

    Measure2 = 
VAR _MaxR = CALCULATE(MAXX(VALUES('Date'[Month Year]),[Measure1]),allselected())
VAR _Avg = DIVIDE(CALCULATE(SUMX(VALUES('Date'[Month Year]),[Measure1]),allselected()),_MaxR)
VAR _Result = power(([Measure1] - _Avg)  ,2)
RETURN

IF(
    [Measure1] < 1,BLANK(),
    _Result 
)

     

     

    or

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    • PVO3's avatar
      PVO3
      Impactful Individual
      4 years ago

      Thanks!

      I changed the REMOVEFILTERS to ALLSELECTED & ^2 to POWER(,2)

      Unfortunatly the result remained the same. 

       

      But perhapse this is an improvement in general.

       

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    PVO3  can you please explain what you are trying to do with Measure2? 

    • PVO3's avatar
      PVO3
      Impactful Individual
      4 years ago

      Hi smpa01 ultimately this should be a part of a slope calculation.

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    4 years ago

    Hi PVO3 ,

     

    Try the measure as below:

    Measure2 =
VAR _MaxR =
    CALCULATE (
        MAXX ( VALUES ( 'Date'[YearMonth] ), [Measure1] ),
        REMOVEFILTERS ()
    )
VAR _Avg =
    DIVIDE (
        CALCULATE (
            SUMX ( VALUES ( 'Date'[YearMonth] ), [Measure1] ),
            REMOVEFILTERS ()
        ),
        _MaxR
    )
VAR _Result = ( [Measure1] - _Avg ) ^ 2
VAR a =
    IF ( [Measure1] < 1, BLANK (), _Result )
RETURN
    SUMX (
        FILTER ( ALLEXCEPT ( 'Date', 'Date'[Date], 'Date'[MonthNumber] ), _Result ),
        a
    )

    If the problem is still not resolved, please point it out. Looking forward to your reply.


    Best Regards,
    Henry


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

    • PVO3's avatar
      PVO3
      Impactful Individual
      4 years ago

      Thanks a lot for your effort v-henryk-mstf 

      Unfortunatly it didnt give the expected result. Tried it in the example file and if gave the result below.

      Or perhapse I'm missing something?

  • PVO3's avatar
    PVO3
    Impactful Individual
    4 years ago

    So, no solution yet. I doubt that this is not possible. Help would be greatly appreciated!

  • PVO3's avatar
    PVO3
    Impactful Individual
    4 years ago

    Nobody has a clue how to do this? Would be great to find a solution.