Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Proud to be a PBI Community Champion
@Anonymous Please provide some sample data which will be helpful to provide an accurate solution.
Proud to be a PBI Community Champion
@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.
Proud to be a PBI Community Champion
@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
@Anonymous You can try creating groups as below, after placing the Stacked Column chart visual on the canvas.
Proud to be a PBI Community Champion
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 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
Proud to be a PBI Community Champion
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |