Create new "all other" row by calculating values of other rows

[Mainer04401]

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.  

[JarroVGIT · Accepted 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! 🙂

[Mainer04401]

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])))