Sum by Group - output question

[Anonymous]

Hi 🙂

I have a fairly simple question - tried searching but my answer may be lost in the countless posts on this.

I have a table that contains purchase orders, and each row is one line of each purchase order. I want to calculate the total of each purchase order as a whole.

Is there a way to group by each PO, and then save the output either as adding a new column, table, or a measure that I can then use?

Data looks like this :

PO Number	PO Line Number	Approved PO Amount	Requestor
12345	1	10	Smith, John
12345	2	2	Smith, John
12346	1	1	Kelly, Mary
12347	1	2	Doe, Jane
12347	2	5	Doe, Jane

Would like to see :

PO Number	PO Lines	Requestor	PO Total
12345	2	Smith, John	12
12346	1	Kelly, Mary	1
12347	2	Doe, Jane	7

Any help would be great, as I am new and keen to learn.

[amitchandak]

Create measures

PO Lines = count(table[PO Line Number])
PO Total = sum(table[Approved PO Amount])

Rest should automatically group in Table and matrix

[Anonymous]

Hi Guys,

Thanks so much for the help, it works great.

Before I mark this as solved, I have another question.

What would be the best way to add a filter to the report so the end user can view only the POs up to or under a certain value?

For example, they want to only see POs up to $5000?

Thanks

[v-gizhi-msft]

Hi,

In your original sample table i can not see any data up to $5000.

But if you want to apply filter to this visual, you can choose the specified field as a slicer, or directly add a filter to Filter Pane.

Best Regards,

Giotto Zhi

[Anonymous]

The table was an example only as the real dataset is huge and contains sensitive information.

The field I want to filter by is the PO Total measure, but it wont allow me to use it on a slicer...?

I cant use the filter pane, I would rather have the user interact with the dashboard directly.

Thanks.

[v-gizhi-msft · Accepted solution]

Hi,

Please try to create a What If parameter such as below:

It will automatically create a slicer.

Then create a check measure:

Check = IF([PO Total]<SELECTEDVALUE(Parameter[Parameter]),1,0)

Apply this measure to the original table visual.

For example, i define a policy that the result only shows the rows which [PO Total] is less than the specified value from slicer.

The result shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[Anonymous]

Thanks so much.

I would not have thought about doing it this way.

[v-gizhi-msft]

Hi,

Please try these two measures:

PO Total = SUM('Table'[Approved PO Amount])

PO Lines = COUNTROWS('Table')

The result shows:

Hope this helps.

Best Regards,

Giotto Zhi

[Anonymous]

edit / nvm - figured it out. I can use the measure and apply a filter to it....