Forum Discussion
Helper IICreate measure with dynamic filters
I have a table with stores - each store has a format type (ex. STANDARD, MEDIUM, MINI). I have total sales for all stores. I want to create a table with all my stores and their formats and divide each store sales to total for each format type, not total sales for all formats.
I have filters for categories. Each store has categories (ex. FOOD, NON-FOOD). When I apply this filter, I want to get sales for each category, divided by total sales for each store.
I also want to divide total sales for each category (global), to total sales for each format type (global).
So basically I need two measurements:
- Total sales for each category/total sales for that format
- Store sales for category / total store sales
This need to be dynamic so the filter must match the store format.
SOLVED:
Here's the solution for who's interested:
Category Sales National =CALCULATE(SUM(Sales[Sales Net Amt]),ALLEXCEPT(Sites,Sites[Site Type 02])) - this will give me a total for each site type for each categoryFormat Sales = CALCULATE(SUM(Sales[Sales Net Amt]),ALLEXCEPT(Sites,Sites[Site Type 02]),REMOVEFILTERS('Merchandise Structure')) - this will give me a total for each site type.Sorry, I'm new to Power BI. I had this made on Excel, but the file became larger and I had to find a solution with Power BI.Thank you parry2k !
11 Replies
AndreiK15SOLVED:
Here's the solution for who's interested:
Category Sales National =CALCULATE(SUM(Sales[Sales Net Amt]),ALLEXCEPT(Sites,Sites[Site Type 02])) - this will give me a total for each site type for each categoryFormat Sales = CALCULATE(SUM(Sales[Sales Net Amt]),ALLEXCEPT(Sites,Sites[Site Type 02]),REMOVEFILTERS('Merchandise Structure')) - this will give me a total for each site type.Sorry, I'm new to Power BI. I had this made on Excel, but the file became larger and I had to find a solution with Power BI.Thank you parry2k !- AndreiK15
parry2k here's the onedrive link:
https://1drv.ms/u/s!AsPHHoPRy-HShdoUb7M36aBroDB71Q?e=WtnXLp
Thank you!
- AndreiK15
parry2k I'm looking to get total sale for site type 02 - if a store is STANDARD 2, to get total sales for STANDARD 2 for all stores. Basically what I want to do is to compare store sales ... in one hand I need to compare Share for each category - which I have it and then I need sales for each site type 02.
Ex. Store 1 - STANDARD 2 - Total Sales for the store - 100k and 25k sales for FOOD - share for FOOD is 25%
I need a column to match site type 02 for each store and get total sales for all stores for that format.
Ex. Store 1 - STANDARD 2 - Total sales for the store - 100k - Total sales for STANDARD 2 format - 500k
Store 2 - CITY 2 - Total sales for the store - 50k - Total sales for CITY 2 format - 250k
Store 3 - STANDARD 2 - Total sales for the store - 75k - Total sales for STANDARD 2 format - 500k
and then I want to divide total sale for all stores for category (ex. FOOD) to total sales for that format
and so on ...hope it make sense
I have the following measures for total sales -
Total sales = CALCULATE([Sales Net Amt AY],REMOVEFILTERS('Merchandise Structure'))Category sales = SUM(Sales[Sales Net Amt])then% Category Share = DIVIDE([Category sales],[Total sales]).This work fine for each store, but I need to compare with global share.hope it make senseThanks!
