value of a brand out of total spend - for stacked chart

[Anonymous]

Hi,

I am trying to create a dax query that calculates the spends sum of a particular brand out of the the whole pool of brands.

So I have a table with a company column e.g company 1, which has a brands column e.g brands A,B,C,D and then spends.

I wanto to see a stacked chart that shows the sum of brands B,C and D and then on top it will show the sum of brand A - to show the proporotion of spends of one brand against all others.

I've tried a few dax querys to calculate the sum but cannot get to being able to filter out the specific brand.

Any help would be great

Thanks

[PattemManohar]

@Anonymous Please provide some sample data which will be helpful to provide an accurate solution.

[Anonymous]

I couldn't upload an excel so this is a screegrab of the basic data

[PattemManohar]

@Anonymous  I understand from your initial post, that you want to group brands B,C,D as one and A as another group. In Stacked Chart you want to the B,C,D group total and on top A total.

But now in your sample data the company2 and 3 doesn't have A,B,C,D brands at all. Is that grouping change for each company ? Also it will be great if you can post your sample data in copiable format.

[Anonymous]

@PattemManohar @- yes there needs to be similar grouping for companies 2 and 3 for their own brands but for now we can just focus on company 1.

Apologies, I can't workout how to attach a file on this forum!

Thanks

Alex

[PattemManohar]

@Anonymous You can try creating groups as below, after placing the Stacked Column chart visual on the canvas.

[Anonymous]

Hi @PattemManohar 

This looks like a great solution so far. Now I want to take it one step further.

Using your data, I have added a 3rd company (CCC).

For company AAA, I have ungrouped brand A, and for company BBB I have ungrouped brand E.

What I want to show for company CCC, is a total for all their spends AND and on top, how their spends would look if they had brand A from company AAA and brand E from company BBB.

Is this possible? So like a sum of brand A and brand E added on top of company CCC's total spend. Basically what company CCC could have if they aquired it.

Thanks for your support on this!

Alex

[Anonymous]

Hi @PattemManohar 

Is my latter query possible?

Any intel would be great thanks!

Alex

[PattemManohar · Accepted solution]

@Anonymous After having the required groupings in place, I took it further the output of that step (in this case it is Test206Grouping table) as per your new requirement. Create a new table as below

Test206GroupingOut = 

VAR _BrandA = LOOKUPVALUE(Test206Grouping[Spend],Test206Grouping[Company],"AAA",Test206Grouping[Brand],"A")
VAR _BrandE = LOOKUPVALUE(Test206Grouping[Spend],Test206Grouping[Company],"BBB",Test206Grouping[Brand],"E")
VAR _Temp = UNION (
                    ROW("Company","CCC","Brand","A","Spend",LOOKUPVALUE(Test206Grouping[Spend],Test206Grouping[Company],"AAA",Test206Grouping[Brand],"A"),"BrandGroups","BCD")
                   ,ROW("Company","CCC","Brand","E","Spend",LOOKUPVALUE(Test206Grouping[Spend],Test206Grouping[Company],"BBB",Test206Grouping[Brand],"E"),"BrandGroups","BCD")
                  )
RETURN UNION(Test206Grouping,_Temp) 

The output looks like

Test206GroupingOutTest206Grouping