Forum Discussion
Measure needing different context
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,
You could change your calculation like this :
CALCULATE( DISTINCTCOUNT('Table'[Store]), FILTER( ALL( 'Table'[Store] ), [measureA] > 0.05 ) )
8 Replies
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/547907Hello 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,
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,
WinnizIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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,
You could change your calculation like this :
CALCULATE( DISTINCTCOUNT('Table'[Store]), FILTER( ALL( 'Table'[Store] ), [measureA] > 0.05 ) )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,
Hmm,
maybe tryCOUNTROWS( FILTER( ALL( 'Table'[Store] ), [measureA] > 0.05 ) )
At this point, I would need to do some more tests with data.
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,
