Power BI - Compare Individual vs Average data per month

[tomcat2018]

Hi, imagine the scenario of sales data per store per month

Is there any way that I would select a store from Slicer and it would compare its individual sales on all month vs the average sales of all stores on all months.

For example: if I select Store B from slicer, then I want a graph that would compare (3,4,5,8) vs (10,11,12,7)

Thanks in Advance!

[Ashish_Mathur · Accepted solution]

Hi,

Using Query Editor, you should first unpivot your dataset to ultimately have a 3 column dataset - Store, Month and Sales.  In a visual of your choice, select any Store in the Filter/slicer and write the following measures:

1. Revenue = SUM(Data[Sales])
2. Total revenue = CALCULATE([Revenue],ALL(Data[Store]),ALL(Data[Month]))

Hope this helps.

[MrNoDaysOff]

Thanks

[tomcat2018]

Thanks for your help.

[MFelix]

Hi @tomcat2018

If you create this two measures then you can do a 3rd measure.with difference, division or something like that.

SALES = SUM(Table[sales])
Total sales = CALCULATE ([SALES] : ALL(Table[Store]; Table[Date]))

Since measures are calculated on context if you place this on your visual should work

Regards
MFelix

[tomcat2018]

thanks for your help

[Ashish_Mathur]

You are welcome.