Forum Discussion
Aggregate percentage column
Hallo Everyone,
I am trying to calculate two columns for the Aggregation and value% of total as of the last two columns in the picture.
The aggregation must be based on the Name, product name ,branch and month dateyear. I have highlighted how each group are.
I have used the earlier DAX but it seems that I am getting only the grand total of everything.
Could someone help me there?
Hi Anonymous ,
Create 2 measures as below:
aggregation = SUMX(FILTER(ALL('Table'),'Table'[Name]=MAX('Table'[Name])&&'Table'[branch]=MAX('Table'[branch])&&'Table'[product name ]=MAX('Table'[product name ])),'Table'[value])value% of total = DIVIDE(MAX('Table'[value]),[aggregation])And you will see:
For the related .pbix file,pls click here.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
6 Replies
- amitchandak
Super User
Anonymous , Try
Measure = divide(Sum(Table[Value]),sum(Table[Aggregation ]))
amitchandak Thank you for your answer, sorry I was not so clear. I am trying to calculate these two columns Aggregation and value%of total.
- amitchandak
Anonymous , Try like
Measure = divide(Sum(Table[Value]),calculate(sum(Table[Value]),allexpect(Table,Table[Name])))
https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/
https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390
Hi Anonymous ,
Create 2 measures as below:
aggregation = SUMX(FILTER(ALL('Table'),'Table'[Name]=MAX('Table'[Name])&&'Table'[branch]=MAX('Table'[branch])&&'Table'[product name ]=MAX('Table'[product name ])),'Table'[value])value% of total = DIVIDE(MAX('Table'[value]),[aggregation])And you will see:
For the related .pbix file,pls click here.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
