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.
I have a table with following fields
What I would like to calculate is the additional online sales in case the shop would sell the same % online per product as all shops together. I was able to generate a summarized table that calculates the "additional online sales"
SummarizedTabel = ADDCOLUMNS ( ADDCOLUMNS ( ADDCOLUMNS ( SUMMARIZE ( Sales; Sales[Product]; Sales[Region]; Sales[Sold by]; "Sales"; SUM ( Sales[Sales] ); "Sales Online"; CALCULATE ( SUM ( Sales[Sales] ); ALLSELECTED ( Sales[Sold by] ); Sales[Sold by] = "Online" ); "Sales Store"; CALCULATE ( SUM ( Sales[Sales] ); ALLSELECTED ( Sales[Sold by] ); Sales[Sold by] = "Store" ); "Total shops Online"; CALCULATE ( SUM ( Sales[Sales] ); ALLSELECTED ( Sales[Sold by]; Sales[Region] ); Sales[Sold by] = "Online" ); "Total shops store"; CALCULATE ( SUM ( Sales[Sales] ); ALLSELECTED ( Sales[Sold by]; Sales[Region] ); Sales[Sold by] = "Store" ) ); "Total shops online vs total"; [Total shops Online] / ( [Total shops Online] + [Total shops store] ); "Selected shop online vs total"; [Sales Online] / ( [Sales Online] + [Sales Store] ) ); "diff total shops vs selected shop"; [Total shops online vs total] - [Selected shop online vs total] ); "additional sales online"; IF ( [diff total shops vs selected shop] < 0; 0; [Sales] * [diff total shops vs selected shop] ) )
But as the original data has more then 1 million rows and I need to apply quite a lot of filters I could like to include the calculation of additional online sales in a measure so that all the report filters get applied (filters based on linked tables). I tried to include the code above in a measure
Addiontional sales = SUMX ( ADDCOLUMNS ( ADDCOLUMNS ( ADDCOLUMNS ( SUMMARIZE ( Sales; Sales[Product]; Sales[Region]; Sales[Sold by]; "Sales"; SUM ( Sales[Sales] ); "Sales Online"; CALCULATE ( SUM ( Sales[Sales] ); ALLSELECTED ( Sales[Sold by] ); Sales[Sold by] = "Online" ); "Sales Store"; CALCULATE ( SUM ( Sales[Sales] ); ALLSELECTED ( Sales[Sold by] ); Sales[Sold by] = "Store" ); "Total shops Online"; CALCULATE ( SUM ( Sales[Sales] ); ALLSELECTED ( Sales[Sold by]; Sales[Region] ); Sales[Sold by] = "Online" ); "Total shops store"; CALCULATE ( SUM ( Sales[Sales] ); ALLSELECTED ( Sales[Sold by]; Sales[Region] ); Sales[Sold by] = "Store" ) ); "Total shops online vs total"; [Total shops Online] / ( [Total shops Online] + [Total shops store] ); "Selected shop online vs total"; [Sales Online] / ( [Sales Online] + [Sales Store] ) ); "diff total shops vs selected shop"; [Total shops online vs total] - [Selected shop online vs total] ); "additional sales online"; IF ( [diff total shops vs selected shop] < 0; 0; [Sales] * [diff total shops vs selected shop] ) ); [additional sales online] )
But when I look at the results it is not what I want to see. The total is correct but I am not able to see the additional sales per product en per region.
How can I solve this? Is there another way to get this solved instead of using summarize?
I am using Power BI for just a few weeks, so I would be surprised if there is a much easier way to calculate the additional online sales.
Thanks in advance for your help on this one...
Kind regards
Brenda
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |