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.
The application I use to pull data is not allowing me to pull all products sales because it's above the maximum amount of data allowed to be pulled. Instead, I can pull select product sales and an aggregate sales of all products. Is there a way in Power BI for me to create a new row in the products column or create a new column that subtracts the sales of the select products from the aggregate sales so that I can have an "all other products" row?
Maybe something like IF [Product] not equal to "Aggregate" then show the rows and values as is, but if [Product] = "Aggregate" then subtract the products sum from the aggregate sum.
Solved! Go to Solution.
Well you were on the right track already! 🙂
Measure =
IF(HASONEVALUE(Table1[Product),
IF(SELECTEDVALUE(Table1[Product]) = "Aggregrate",
CALCULATE(SUM(Table1[Sales], FILTER(ALL(Table1), Table1[Product] = "Aggregrate"))-
CALCULATE(SUM(Table1[Sales], FILTER(ALL(Table1), Table1[Product] <> "Aggregrate")),
CALCULATE(SUM(Table1[Sales], FILTER(ALL(Table1), Table1[Product] = SELECTEDVALUE(Table1[Product]))))
Something like this might work (note: untested and written without intellisense). It basically checks if, in the current filtered context, Product has only 1 value and if that value is Aggregate, it sums everything with 'agregrate' and subtracts everything else (not Aggregrate). Otherwise, sum sales of the current selected value of Product.
Let me know if this works for you 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Well you were on the right track already! 🙂
Measure =
IF(HASONEVALUE(Table1[Product),
IF(SELECTEDVALUE(Table1[Product]) = "Aggregrate",
CALCULATE(SUM(Table1[Sales], FILTER(ALL(Table1), Table1[Product] = "Aggregrate"))-
CALCULATE(SUM(Table1[Sales], FILTER(ALL(Table1), Table1[Product] <> "Aggregrate")),
CALCULATE(SUM(Table1[Sales], FILTER(ALL(Table1), Table1[Product] = SELECTEDVALUE(Table1[Product]))))
Something like this might work (note: untested and written without intellisense). It basically checks if, in the current filtered context, Product has only 1 value and if that value is Aggregate, it sums everything with 'agregrate' and subtracts everything else (not Aggregrate). Otherwise, sum sales of the current selected value of Product.
Let me know if this works for you 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Worked brilliantly, thank you! I changed the formula a bit because the TOTALS weren't populating. I remove the HASONEVALUE string and everything still appears to work as need.
IF(SELECTEDVALUE(Table1[Product]) = "Aggregate", CALCULATE(SUM(Table1[Sales]), FILTER(ALL(Table1), Table1[Product] = "Aggregate"))- CALCULATE(SUM(Table1[Sales]), FILTER(ALL(Table1), Table1[Product] <> "Aggregate")), CALCULATE(SUM(Table1[Sales]))) |
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |