Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape 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.
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!
Solved! Go to 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
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])
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])
Hi @christinaxxx ,
Please try:
PercentageMeetingRequirement = IF(ISINSCOPE(dimStore[Store]),
DIVIDE(
[CountOfMustSellingSKU],
COUNTROWS(dimMustSellingSKU),
0
),AVERAGEX(dimStore,DIVIDE(
[CountOfMustSellingSKU],
COUNTROWS(dimMustSellingSKU),
0
)))
Best Regards,
Bof
Thank you, it works!
Thanks Bof. I will have a look at this function.
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) )
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 🙂
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:
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%.
User | Count |
---|---|
93 | |
90 | |
90 | |
81 | |
49 |
User | Count |
---|---|
156 | |
145 | |
104 | |
72 | |
55 |