The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Need help on below scenario, I have table and slicers like below.
Table 1 | ||
Owner | Product | Sales |
A | p1 | 4000 |
A | p2 | 6000 |
A | p3 | 10000 |
A | p4 | 2000 |
B | p5 | 8000 |
B | p6 | 3000 |
B | P7 | 9000 |
Slicer 1(created table with distinct values of Owner from table 1)
Old Owner |
A |
B |
Slicer 2 (created table with distinct values of product from table 1)
Product |
P1 |
P2 |
P3 |
P4 |
Slicer 3 (duplicate copy of Slicer 1 for user display purpose)
New Owner |
A |
B |
Requirement is to show updated sales with respect to B when particular prodcuts changed from A to B in case 1 & case 2 scenarios as below.
For example when i select Owner A from Slicer 1 and select p1 & p2 prodcuts from Slicer 2
and if i assign p1 & p2 to B by selecting in slicer 3 need to analyze the sales with respect to owner A and owner B
case 1: expected output table or chart: Analyzing sales prodcuts changes from Owner A to B, along with existing prodcuts of B
Newowner old&newproduct updatedsales1
B p5 8000 (exsiting prodcut sales of B)
B p6 3000 (exsiting prodcut sales of B)
B p7 9000 (exsiting prodcut sales of B)
B p1 4000 ( p1 sales(4000) newly assigned from A to B)
B p2 6000 ( p2 sales(6000) newly assigned from A to B)
case 2: expected output table or chart : Need to show what are remaining products from Owner A list,in slicer 2 products p1 & p2
are moved to Owner B then remain prodcuts P3,p4, here analysing existing prodcuts how much sales it is.
Oldowner product updatedsales2
A p3 10000 ( as per table 1 data)
A p4 2000 ( as per table 1 data)
created measure for case 1 like below but didnt get expected output
updatedsales1= var previousowner=selectedvalue(products1[owner]) ----slicer 1 selection
var product=Allselected(products[product]) --slicer 2 selection (single or multiple selection)
var newowner=selectedvalue(products2[owner]) ----slicer 3 selection
return calculate(sum(Table1[sales]),FILTER(Table1[Owner]=previousowner))
+calculate(sumX(Table1[sales]),FILTER(Table1[product]=product and FILTER(Table1[Owner]=newowner)))
created measure for case 2 like below but didnt get expected output.
updatedsales2=calculate(sum(table1(sales),except(all(table1([product]),allselected(table1([product])))
by using above measure i am getting overall sum 12000 but i need to split products p3 p4 ( p3 10000,p4 2000) like above case 2
Please go through the deatils, help me and suggest with updated DAX measures in above 2 cases with proposed solutions.
Thank you for your time and help in advance.
Thank you,
Mahi18
Hi @Mahi1827
If the above posts help, please kindly mark it as a answer to help others find it more quickly. thanks!
If not, please kindly elaborate more.
One way to do this is to make a second table of your Owner table and use the Basket Analysis pattern described by SQLBI here.
https://www.daxpatterns.com/basket-analysis/
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
97 | |
80 | |
62 | |
57 |
User | Count |
---|---|
262 | |
120 | |
115 | |
83 | |
70 |