Multiple contexts within iterator

[PVO3]

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 
)

[PVO3]

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

[PVO3]

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

[v-henryk-mstf]

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]

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?

[smpa01]

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

[PVO3]

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

[amitchandak]

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

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.

[amitchandak]

@PVO3 , Please share sample in table format , need to try this in power bi

[PVO3]

Thanks a lot for your effort.

I created an example. Link in first post.