Forum Discussion
Group by Aggregate
Hi everyone
I am required to have a group by total in my report.
There are several rows of same supplier with different invoices total, I want to group all rows into one row and show the sum of invoices in that row. For example see the picture. I want the G.Total 47682 to be shown against Supplier Name "Seven Star" in a single row, and not the way it looks like below. I am using Direct query in my report .Can someone help me ?
Best wishes
You can write measure like below:-
Measure_LT_125 = if(sum(total amount)<125000,sum(total amount))Measure_GT_700 = if(sum(total amount)>700000,sum(total amount))Measure_BT = if(AND(sum(total amount)>125000,sum(total amount)<700000),sum(total amount))
6 Replies
Hey Anonymous ,
If your example isn't what you want it to look like, then can you show us what you want the results to look like?
Hi Anonymous ,
There are two ways to do it:-
1. You can create a two measure like below and use it on table visual:-
Sum_Total_invoice = Sum(table[Total invoice ])Sum_Total = Sum(table[Total])2. Or you can directly drag these column on table visual and put summerization as sum for total and total invoice:-
Thanks,
Samarth
Hi Samarth
Thankyou for tips. Can you also tell how to create measure like less than <125k, greater than >700 k and between 125k-700k .. As you can see below, i created one but it shows amount of 388 883, which is wrong.
it should be all invoices together under 125k...
Thanks
You can write measure like below:-
Measure_LT_125 = if(sum(total amount)<125000,sum(total amount))Measure_GT_700 = if(sum(total amount)>700000,sum(total amount))Measure_BT = if(AND(sum(total amount)>125000,sum(total amount)<700000),sum(total amount))
