Calculate the average of a percentage measure for higher hierarchy in the matrix

[christinaxxx]

I have a must selling SKU table of 20 products, a store table with corresponding region, and a fact sales table that contains the transactional data. I want to calculate the percentage of meeting the must selling requirement for each store as well as showing the regional average in matrix. Example below is what I want to achieve. 

The problem I have right now is that I can't get the 73% - I can only get 100% (which is not correct). 

Below are measures that I have created. Both are only correct at the store level but not regional level.

CountOfMustSellingSKU =
COUNTROWS(
    FILTER(
        dimMustSellingSKU,
        COUNTROWS(
            FILTER(factSales, factSales[product] = dimMustSellingSKU[product] && factSales[customertype] = "standard"
            )
        ) > 0
    )
)

PercentageMeetingRequirement = 
DIVIDE(
    CountOfMustSellingSKU,
    COUNTROWS(dimMustSellingSKU),
    0
)

I have tried AVERGEX but it didn't work.

avg = 
AVERAGEX(VALUES(dimStore[Region]), PercentageMeetingRequirement)

EDIT: here's link to the sample data 

Thank you so much for your help!

[v-bofeng-msft]

Hi @christinaxxx ,

Would you like to calculate the average of each store’s percentage at the Region level? If so, the calculation method differs between the Region level and the store level. At the store level, we only need to calculate the percentage within each category, while at the Region level, we need to first calculate the store-level percentages, then sum them up and take the average. Since these two levels require different calculation logic, the measure will need to determine the current level and apply the appropriate calculation accordingly.

In Power BI’s matrix visual, you can use the ISINSCOPE function to detect which hierarchy level (like rows or columns) is expanded, allowing you to apply different calculation logic at each level. For example:

Custom Measure =
IF(
ISINSCOPE('Table'[Region]),
-- If at the Region level, apply specific calculation for Region
CALCULATE(XXXXXXX),

IF(
ISINSCOPE('Table'[Store]),
-- If at the Store level, apply specific calculation for Store
CALCULATE(XXXXXXX),

-- At higher levels, apply a different calculation
CALCULATE(XXXXXXX)
)
)

If the issue persists, please consider sharing the PBIX file (with any sensitive data removed or replaced with sample data).

Best Regards,

Bof

[christinaxxx]

Hi Bof, I did a quick test on the ISINSCOPE function and wrote the following DAX. However, I got TRUE at both store and region levels (apologies I can't show the actual row or column headers, but rows where not greyed out are store level).

ISINSCOPE = ISINSCOPE('dimStore'[Region])

[v-bofeng-msft]

Hi @christinaxxx ,

You might want to first check the store, as it is also influenced by the region filter.

ISINSCOPE = ISINSCOPE('dimStore'[Store])

Best Regards,

Bof

[christinaxxx]

Thanks Bof. Results are correct at the store level with this measure. But I think the bigger problem I have is that I don't know how to sum the store percentages up and take the average for region level.

ISINSCOPE = ISINSCOPE('dimStore'[Store])

[v-bofeng-msft · Accepted solution]

Hi @christinaxxx ,

Please try:

PercentageMeetingRequirement = IF(ISINSCOPE(dimStore[Store]),
DIVIDE(
    [CountOfMustSellingSKU],
    COUNTROWS(dimMustSellingSKU),
    0
),AVERAGEX(dimStore,DIVIDE(
    [CountOfMustSellingSKU],
    COUNTROWS(dimMustSellingSKU),
    0
)))

Best Regards,

Bof

[christinaxxx]

Thank you, it works!

[christinaxxx]

Thanks Bof. I will have a look at this function.

[vojtechsima]

Hello, @christinaxxx ,
if you want Average for the region, you can do this:

sumCount = SUM('Table'[count])

avgResion = AVERAGEX(VALUES('Table'[region]), DIVIDE([sumCount], 20) )

[christinaxxx]

Thank you. However the count is a measure not a column so this doesn't work.

[vojtechsima]

Hi, @christinaxxx 
then remove my sumCount and reference your own measure, Averagex in this way doesn't require a column.

[christinaxxx]

I mentioned AVERAGEX didn't work for me in the post - just added the DAX. Not sure whether that was correct though.

[vojtechsima]

Hello, @christinaxxx ,

could you perhaps attach or sent link to some sample data, please?

[christinaxxx]

Sample data here's the link to the sample data. Let me know if the link doesn't work. Thank you 🙂

[vojtechsima]

@christinaxxx make it public please.

[christinaxxx]

oops sorry just did.

[vojtechsima]

@christinaxxx  I checked your data, but I think the data doesn'T really well represented what you showed earlier as I got this:

your measure you posted looks fine, you just didn't add the [] for referencing measure:

avg = 
AVERAGEX(VALUES(dimStore[Region]), PercentageMeetingRequirement)

[christinaxxx]

The percentage calculation should be mustSellingSKUfromfact / mustSellingSKUfromProduct (e.g., 2/22=9% for Store 1). But even in your screenshot, 450% is not correct - I need the average of store %'s which is 150%.