Create measure with dynamic filters

[AndreiK15]

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:

1. Total sales for each category/total sales for that format
2. Store sales for category / total store sales

This need to be dynamic so the filter must match the store format.

[parry2k]

@AndreiK15 good to hear it is resolved. Don't want to go into detail. Next time when you have a question, Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[AndreiK15 · Accepted solution]

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 category

Format 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 sent you a private msg with the excel file.

Thanks!

[parry2k]

@AndreiK15 wow, your naming is all over the place. Simple thing looking so complicated. Why not you put sample data in excel and throw formulas there. You know your data better than I do, certain things are obvious for you , not for me. 

[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 sense

Thanks!

[parry2k]

@AndreiK15 are you looking for something like this, your description and column names don't match and it is all very confusing. You should add some expected results so that it is easy to provide the solution rather than spending time going back and forth.

[AndreiK15]

@parry2k here's the onedrive link:

https://1drv.ms/u/s!AsPHHoPRy-HShdoUb7M36aBroDB71Q?e=WtnXLp

Thank you!

[parry2k]

@AndreiK15 unfortunately nope, can you share thru One drive/google drive

[AndreiK15]

@parry2k can you access the link?

[AndreiK15]

Hi,

Please find the link

Thank you!

[parry2k]

@AndreiK15 it will be easier to put the solution together if you put a sample pbix file with the expected output.