compare store sales per article, to other stores of the same size (std. dev, average)

[MegaOctane1]

Hi guys,

I have a table in excel where i calculate Average, Max and Standard deviation.
So what i want to do is:

Compare the current stores sale of each article to other stores that are same in size. This works in Excel, but we want to replicate this to Power BI. I want to create a table visual in Power BI to show the data

Average has the formula: =AVERAGE(FILTER([Qty];([StoreSize]=[@StoreSize]) * ([Store]<>[@Store]) * ([Article]=[@Article])))
Max: =MAX(FILTER([Qty];[Store]=[@Store]))
Std dev: =STDEV.P(FILTER([Qty];([StoreSize]=[@StoreSize]) * ([Store]<>[@Store]) * ([Article]=[@Article])))

Article	Store	StoreSize	Month	Qty	Average	Max	Std dev
560455	4001	small	jan.25	2,00	1,67	2	0,94
560455	4001	small	feb.25	1,00	1,67	2	0,94
560455	4002	small	jan.25	1,00 EA	1,50	3	0,50
560455	4002	small	feb.25	3,00 EA	1,50	3	0,50
560455	4002	small	mar.25	1,00 EA	1,50	3	0,50
571512	4003	medium	jan.25	2,00	1,00	4	0,00
571512	4003	medium	feb.25	4,00	1,00	4	0,00
571512	4004	small	jan.25	2,00 EA	5,50	4	1,50
571512	4004	small	feb.25	4,00 EA	5,50	4	1,50
571512	4004	small	mar.25	1,00 EA	5,50	4	1,50
571512	4004	small	apr.25	2,00 EA	5,50	4	1,50
571512	4005	small	feb.25	7,00 EA	2,25	7	1,09
571512	4005	small	apr.25	4,00 EA	2,25	7	1,09
571512	4006	medium	jan.25	1,00 EA	3,00	1	1,00
571512	4006	medium	feb.25	1,00 EA	3,00	1	1,00
571512	4006	medium	mar.25	1,00 EA	3,00	1	1,00
571512	4006	medium	apr.25	1,00 EA	3,00	1	1,00

[v-ssriganesh]

Hi @MegaOctane1,
Thank you for reaching out to the Microsoft Fabric Forum Community.

I have reproduced your scenario in Power BI Desktop and achieved the expected output as per your requirement. For your reference, I’m attaching the .pbix file that contains the complete implementation, including the Z-Score calculation logic and formatted table output.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[lmolus]

Hi, 

try this:

VAR _article = MAX( 'Table'[Article] ) 
VAR _store= MAX( 'Table'[Store] )
VAR _storesize = MAX( 'Table'[StoreSize] )
VAR Result = 
CALCULATE( 
    AVERAGE( 'Table'[Sales] ) , 
    FILTER( 
        ALL( 'Table'[Article] , 'Table'[Store] , 'Table'[StoreSize] ) , 
        'Table'[Article] = _article 
            && 'Table'[StoreSize] = _storesize
            && 'Table'[Store] <> _store
    )
)
RETURN
    Result

It should work, you can calculate ST.DEV. and MAX using simillar approach.

[MegaOctane1]

I tried with the sample data i provided. The calculation when used in a table view, returns blanks

MeasureMAX = 
VAR _article = MAX( 'tblSales'[Article] ) 
VAR _store= MAX( 'tblSales'[Store] )
VAR _storesize = MAX( 'tblSales'[StoreSize] )
VAR Result = 
CALCULATE( 
    AVERAGE( 'tblSales'[Delivered] ) , 
    FILTER( 
        ALL( 'tblSales'[Article] , 'tblSales'[Store] , 'tblSales'[StoreSize] ) , 
        'tblSales'[Article] = _article 
            && 'tblSales'[StoreSize] = _storesize
            && 'tblSales'[Store] <> _store
    )
)
RETURN
    Result

My data shows blank:

Here is a link to sample data:
https://www.dropbox.com/scl/fi/oqdurfcgeywydrtdjd15w/Sample_data.xlsx?rlkey=b6d1610mw70m2hzb9j9oyo20...

[lmolus]

Do you have any relationships in your semantic model, that might be used in a table (from your screen)?

It would be much easier if you could send a .pbix file with sample data.

[ajaybabuinturi]

Hi @MegaOctane1,

Can you try with below formula. Let me know if you have any questions.

AvgQty_OtherStores = 
CALCULATE(
    AVERAGE(Sales[Delivered]),
    FILTER(
        ALL(Sales),
        Sales[StoreSize] = SELECTEDVALUE(Sales[StoreSize]) &&
        Sales[Store] <> SELECTEDVALUE(Sales[Store]) &&
        Sales[Article] = SELECTEDVALUE(Sales[Article])
    )
)

MaxQty_CurrentStore = 
CALCULATE(
    MAX(Sales[Delivered]),
    FILTER(
        ALL(Sales),
        Sales[Store] = SELECTEDVALUE(Sales[Store]) &&
        Sales[Article] = SELECTEDVALUE(Sales[Article])
    )
)

StdDevQty_OtherStores = 
CALCULATE(
    STDEV.P(Sales[Delivered]),
    FILTER(
        ALL(Sales),
        Sales[StoreSize] = SELECTEDVALUE(Sales[StoreSize]) &&
        Sales[Store] <> SELECTEDVALUE(Sales[Store]) &&
        Sales[Article] = SELECTEDVALUE(Sales[Article])
    )
)

Max # Std from Avrg1 = 
DIVIDE((Sales[MaxQty_CurrentStore] - Sales[AvgQty_OtherStores]), Sales[StdDevQty_OtherStores], BLANK())

• Ensure your data model is not filtering out relevant data via slicers or relationships. The SELECTEDVALUE functions rely on proper row context.
• If your model has separate dimension tables (for stores, articles), you might need to adjust context using RELATED or TREATAS.

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[MegaOctane1]

Hi and thank you for a quick reply.
I tried the first formula, and it didn't work unfortunately. it returns blank on all lines

[ajaybabuinturi]

@MegaOctane1 Could you please provide some sample data.

[MegaOctane1]

Here is sample data in Excel. i have also attached the formulas we are using.

https://www.dropbox.com/scl/fi/oqdurfcgeywydrtdjd15w/Sample_data.xlsx?rlkey=b6d1610mw70m2hzb9j9oyo20...

[ajaybabuinturi · Accepted solution]

Hi @MegaOctane1,

I made small tweak to the measures now it is working fine. Also I am attching .pbix file for your reference. I update the measures in original/intitial responce post.

Here is the .pbix file.

https://drive.google.com/file/d/16dFidVfzLkrNnHzvhUbM4UJy_VYgmmNs/view 

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[MegaOctane1]

PERFECT!!!!