Share per brand of total sales

[Anonymous]

Hello!

I would like to make a table but I don't know if it is possible. With below dataset (end of the message) I can make the following matrixes:

So in here you can see all brands and the share of that brand of the total sales. It is filtered on the highest sales in 2020 with the following DAX: 

Salessum =

IF(

HASONEVALUE('Testfile 2'[Fiscal Year]),SUM('Testfile 2'[Sales]),

CALCULATE(SUM('Testfile 2'[Sales]),'Testfile 2'[Fiscal Year] = 2020))

And I made the following matrix:

This is the same matrix but then only with the top 3 brands. 

But I want to see a combination of the 2 matrixes: I want to see the sales numbers from the second matrix and the % share numbers from the first matrix so that I know how much sales we do with the top 3 brands. In the end it will look like this:

The 49% is the share of the 3 brands from the total sales of 23.500 in 2020.

I hope this is possible.

Thanks a lot!

Maartje

Fiscal Year	Brand	Sales
2019	A	1000
2019	A	800
2019	A	1100
2019	A	1200
2019	B	600
2019	B	1500
2019	B	900
2019	B	1300
2019	C	1400
2019	C	500
2019	C	300
2019	C	800
2019	D	1400
2019	D	300
2019	D	1500
2019	D	1100
2019	E	300
2019	E	1500
2019	E	1100
2019	E	1000
2019	F	200
2019	F	400
2019	F	100
2019	F	300
2019	G	1000
2019	G	900
2019	G	700
2019	G	800
2019	H	200
2019	H	200
2019	H	200
2019	H	200
2020	A	800
2020	A	500
2020	A	1200
2020	A	600
2020	B	1000
2020	B	900
2020	B	1000
2020	B	1200
2020	C	800
2020	C	400
2020	C	800
2020	C	1100
2020	D	600
2020	D	1100
2020	D	900
2020	D	800
2020	E	1200
2020	E	500
2020	E	800
2020	E	800
2020	F	200
2020	F	100
2020	F	100
2020	F	100
2020	G	1000
2020	G	1500
2020	G	700
2020	G	800
2020	H	800
2020	H	800
2020	H	200
2020	H	200

[ryan_mayu]

@Anonymous 

pls see the attachment below

[Anonymous]

Hi @ryan_mayu,

Thanks for your help. This is indeed what I wanted but I also want to rank it on sales in 2021 and I only want to see the sales for the weeks in the filter. I figured that out by making the following formula (Because I have some other tables etc in my real dataset it might look a bit different): 

Share '21 = [Ecom Value 2021]/CALCULATE(SUM(Actuals[ value ecom ]), FILTER(ALL(Actuals), Actuals[Fiscal Year] = max(Actuals[Fiscal Year]) && Actuals[Fiscal Week Number]<=max(Actuals[Fiscal Week Number])))

Ecom value 2021 is also a calculation: 

CALCULATE([Total Value Ecom],Actuals[Fiscal Year] = 2021)

But besides this, I want the formula to look at 1 other thing: I have a filter of shipment type and in here I have 1 shipment type I want to exclude at any time. So I thought I had to add FILTER (ALLSELECTED) for this one, but that doesn't work. 

So the above formula is working but I want to add the following filter:

In which CL is excluded but the rest is included. 

Any idea how I can do that?

Thanks a lot!

Maartje

 

[v-shex-msft]

Hi @Anonymous,

Perhaps you can try to write a measure formula with date function to filter records and use it on 'visual level filter'.

Applying a measure filter in Power BI - SQLBI

Regards,

Xiaoxin Sheng

[ryan_mayu]

@Anonymous 

have you tried allexcept(actual,[shipment type])

[Anonymous]

@ryan_mayu 

I did change the formula now to ALLEXCEPT and it is working with all the other filters but it is not working with the filter Shipment Type. I used the following formula:

Share '21 test = [Ecom Value 2021]/CALCULATE(SUM(Actuals[ value ecom ]), ALLEXCEPT(Actuals, Actuals[Shipment quantity], Actuals[Fiscal Year], Actuals[Fiscal Week Number],Actuals[ Business unit ]))

If I have these filters on:

it gives this result (I removed the brand name in front of the Ecom value 2020 because that is sensitive information): 

If I add CL in the filter, so I use the following filters:

I do get 100%:

If I change the week numbers or business unit, it is working. But I only get 100% if I select CL as well. Any idea?

Thanks!

[ryan_mayu]

@Anonymous 

based on the screenshot you provided in the last post. I think allselected should work for this.

Is it possible to remove sensitive data and provide the pbix file?

[Anonymous]

@ryan_mayu 

It took a while because there was so much sensitive data in it that I had to rebuild everything. But now it seems like I can't attach the pbix. Do you know how I can do that in this forum?

Thanks a lot!

Maartje