Group by Aggregate

[Anonymous]

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 

 

[Samarth_18]

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

[Anonymous]

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

[Samarth_18 · Accepted solution]

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

[Anonymous]

Great. Sometime solution is easier than one think.. Thankyou for solution.

[Watsky]

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?

[Anonymous]

Hi Watsky

Thankyou for correction. Here is a new example how i want it to be look like  

Explanation ::: Sevenstar has many rows, I want it to be groupped in one row and show the total invoices and total .

 

Best regards