Measure needing different context

[Anonymous]

Dear community, 

I would appreciate some help in the following issue.

I have aggregated data about some stores. I have several more columns describing the stores but, for the sake of the example let's assume:

Year	Store	Country	…	Amount($)	Performance
2011	A	US	…	100	30
2011	A	MX	…	200	20
2011	B	US	…	150	25
2011	B	MX	…	130	10
2011	B	TZ	…	190	13
2012	A	US	...	30	5

I compute the following measure:

measureA :=

DIVIDE (

  SUM ( Table[Performance] ),

  CALCULATE (

    SUM ( Table[Amount($)] ),

    ALL ( Table[Store] )

  )

)

Basically it computes a kind of average for each store to compare it with the others considering the filter context of the chart (I want a matrix in particular).

This will work AS LONG AS I include the Store in the matrix chart I am building. If not, I would get a value that does not make any sense. 

So, for example, if I create a matrix chart with Columns Year and Store I get:

Year	Store	measureA
2011	A	0.06
2011	B	0.04
2012	A	0.17

Which is fine. But now I have 2 questions (related to each other):

First question:

I want to create a measureB that counts how many rows satisfy measureA > 0.05 for the current context EXCEPT for the store. In the example above, I would like the table:

Year	Store	measureA	measureB
2011	A	0.06	1
2011	B	0.04	1
2012	A	0.17	1

(In year 2011, only store A satisfies measureA > 0.05. Then I want a "1" in all rows that have Year = 2011. Same reasoning for 2012).

Second question:

I want to create a matrix chart taking for example Year (NOT Store) and want to count how many Stores satisfy for example measureA > 0.5

So I want the following table:

Year	measureC
2011	1
2012	1

Any ideas?

Sincerely,

[m3tr01d · Accepted solution]

You could change your calculation like this :

CALCULATE(
    DISTINCTCOUNT('Table'[Store]),
    FILTER(
        ALL( 'Table'[Store] ),
        [measureA] > 0.05
    )
)

[Anonymous]

Hello @m3tr01d , 

In fact, the measure above solve both questions, the result does not change if I add or delete the Store column to the table.

I will open another post because I do not want to add noise, but I really do not understand what is happening here behind the scenes and why does this work. If anybody would be so kind as to stop by I would appreciate it. 

Best regards,

[Anonymous]

Hello @m3tr01d ,

Thank you very much for your response.

This solved me the problem of measureB but still cannot figure out how to compute measureC, which unfortunately is my final table.

Any ideas would be much appreciated.

Best regards,

[m3tr01d]

Hmm,
maybe try 

COUNTROWS(
    FILTER(
        ALL( 'Table'[Store] ),
        [measureA] > 0.05
    )
)

 
At this point, I would need to do some more tests with data.

[Anonymous]

Hello @Greg_Deckler 

Thanks for your reply, I have spent a lot of time and I am sorry but I am not figuring this out. I cannot see how to combine HASONEFILTER() and maybe SUMMARIZE() to get both results I want. 

In fact, my attempts were working in the following direction:

For the first question:

measureB = 
CALCULATE(
    DISTINCTCOUNT('Table'[Store]),
    ALL('Table'[Store]),
    [measureA] < 0.05
)

But I get the error:

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed 

For the second question no idea...

Regards,

[v-kkf-msft]

Hi @Anonymous ,

For the first question, try the following formula:

MeasureB = IF( MAXX( ALLEXCEPT( 'Table', 'Table'[Year] ), [MeasureA] ) > 0.05, 1 )

And for the second question, try the formula provided by @m3tr01d.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

---

[Anonymous]

Dear @v-kkf-msft ,

Thank you for your answer. I am afraid the solution you propose appears to be correct but it is not. I have the impression the result is correct "by chance", and it is something I had observed before:

If I change slightly your measure to:

MeasureB-2 = MAXX( ALLEXCEPT( 'Table', 'Table'[Year] ), [MeasureA] )

just to check what Power BI is computing in this MAXX, I do not get what I expect:

The result for the last column should be (0.06, 0.06, 0.17). After doing some tests, I guess that what Power BI is computing under the hood is [measureA] NOT in the current context, but for each row of the original Table, and then computing the maximum of all the rows (in this case, the maximum IS INDEED computed given the current context).

After reading a bit about it, I came to the conclusion that iterator functions like MAXX() "get rid" of all the context and compute all the expressions row by row.

Of course when you compute [measureA] not in the current context but just row by row, you get just a simple division like I show in the Excel capture in column Perf/Amount. But this is wrongly computed.

As a matter of fact by the way, I believe it should be:

ALL('Table'[Store])

instead of 

ALLEXCEPT( 'Table', 'Table'[Year] )

Don't you think so?

@m3tr01d formula solved the problem for question 1:

CALCULATE(
    DISTINCTCOUNT('Table'[Store]),
    FILTER(
        ALL( 'Table'[Store] ),
        [measureA] > 0.05
    )
)

But question 2 remains unsolved (which unfortunately is the final table in my report...).

Best wishes,

[Greg_Deckler]

@Anonymous I am thinking that this is essentially a measure totals problem. This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907