Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
christinaxxx
Helper I
Helper I

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

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. 

christinaxxx_0-1730712253664.png

 

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

christinaxxx_0-1730713153965.png

 

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!

1 ACCEPTED SOLUTION

Hi @christinaxxx ,

 

Please try:

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

vbofengmsft_0-1731050824067.png

 

Best Regards,

Bof

View solution in original post

17 REPLIES 17
v-bofeng-msft
Community Support
Community Support

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

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

 

christinaxxx_0-1730946115872.png

 

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

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

christinaxxx_0-1731007572860.png

 

Hi @christinaxxx ,

 

Please try:

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

vbofengmsft_0-1731050824067.png

 

Best Regards,

Bof

Thank you, it works!

christinaxxx_0-1731368380702.png

 

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

vojtechsima
Resident Rockstar
Resident Rockstar

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

vojtechsima_0-1730714144390.png

 

 

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

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

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

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

Hello, @christinaxxx ,

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

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

@christinaxxx make it public please.

oops sorry just did.

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

vojtechsima_0-1730842547168.png

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

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

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%.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.