How to create subtotals as new row from multiple id

[azura]

dear all

I currently have some problems to create subtotal as new row and create total from subtotal as new row. I have multiple IDs with a full row with different id.

here is my table and the expected output

https://drive.google.com/open?id=1Mk-GKsXgmJMTPeUtXNUloBK5FeIcNPKd

how i can do that in power BI,

thank you in advance 

azura

[v-frfei-msft · Accepted solution]

Hi @azura ,

To createa some calculated tables as below.

mixd = SUMMARIZECOLUMNS(Table1[Year],"Productid",MAX(Table1[Productid]),"productname","mixd","amount",SUM(Table1[amount]))

fruits = SUMMARIZECOLUMNS(Table1[Year],"Productid",MAX(Table1[Productid]),"Productname","Fruits","amount",CALCULATE(SUM(Table1[amount]),FILTER(Table1,Table1[producename]="apple" || Table1[producename]="banana")))

vagetables = SUMMARIZECOLUMNS(Table1[Year],"Productid",MAX(Table1[Productid]),"Productname","vegaables","amount",CALCULATE(SUM(Table1[amount]),FILTER(Table1,Table1[producename]="tomatoes" || Table1[producename]="brocoli"))) 

Table = UNION(Table1,mixd,fruits,vagetables)

[azura]

Hi Frank;

thanks for your response, the real dataset more complex than the sample, currently i'am on going prosess to solved this with your guidace, i got the idea, and it worked in my dataset;

but i missed some calculation, like subrtraction; ( sub Mixed )

is it possible to subtracted fruit - vegetables;

i tried to create table which group by and amount is calculated filter fruit - calculated filter vegetables  But Got Error;

Sub Mixed = SUMMARIZECOLUMNS(Table1[Year],"Productid",MAX(Table1[Productid]),"Productname","Fruits","amount",CALCULATE(SUM(Table1[amount]),FILTER(Table1,Table1[producename]="apple" || Table1[producename]="banana") - CALCULATE(SUM(Table1[amount]),FILTER(Table1,Table1[producename]="brocoli" || Table1[producename]="tomatoes"))))

best regards;

Azura