I am having issues getting a row count of results based on a measure being filtered by another measure.
I'm calculating an average in a client hierarchy where the most aggregated element will be the store, then chain, and finally channel. So I want to compare the average of the store against the average of the chain to which the store belongs to.
I'm using this measure that works fine:
210. Facing Avg Store =
VAR V1 = [201. Facing Avg]
VAR V2 = CALCULATE([201. Facing Avg], ALL(DIM_CLIENT_VW[DESC_CHAIN]), ALL(DIM_CLIENT_VW[DESC_STORE]))
VAR V3 = CALCULATE([201. Facing Avg], ALL(DIM_CLIENT_VW[DESC_STORE]))
RETURN
IF(HASONEFILTER(DIM_CLIENT_VW[DESC_STORE]),V3,
IF(HASONEFILTER(DIM_CLIENT_VW[DESC_CHAIN]),V2, V1))
Then, I need to count the Stores that are above avg and the stores below avg. To identify them, I'm subtracting the Average minus the Store Avg and creating a flag, depending on if the result is above or below zero like this:
210. Benchmark above/below store =
VAR V1 = [201. Facing Avg] - [210. Facing Avg Store]
RETURN
IF(V1 > 0, 1, IF(V1 < 0, -1, BLANK()))
The result is ok, I'm able to identify which stores are above and which are below.
The issue is when I try to count the flag and show it on a Card object or use it for another calculation, and I think is because of the aggregation on the original measure [210. Facing Avg Store], If I don't have stores in the axis, the total is blank because any of the conditions are fulfilled.
Is there a way I can get the result I'm looking for? I've tried using a table and filter the table by the flag, and the number I obtain is ok, but I can't use the result in any calculations and I need it to known the % of stores below avg and the % above avg.
Hope you can help me,
Mijalis
