Combine and average invoice numbers and cost

[Jcolvile]

Hey all,

I am just having a little issue with a table and how to aggrigate it. The table is layed out as follows:

Invoice Number	Invoice Cost
A	6
A	6
A	6
B	45
B	45
B	45
C	10
C	10
C	10
C	10

For the dashboard visual I need to display the total invoice cost ($61). How would I go about aggrigating this?

Thanks in advance,

Joe

[dkay84_PowerBI]

Why do you have duplicate rows?  If you have one record per invoice number, it would be a simple sum of the Invoice Cost field.  You can still create a measure that calculates the sum based on the max (or min, or average, or whatever gets you a singular value) Invoice Cost per Invoice Number, but perhaps rethink why you are using up memory for duplicate rows?

[Jcolvile]

I have multiply lines of the same data due to each stock unit being marked off against the invoice but the individual invoice cost for the unit not being logged. This is the way the background table was built, annoying but what I have to work with.

I can create a separate table which combines the invoices to a singular value for each IN# but due to other data in the table and the need to use visual filters splitting the data into a separate table is not an option. 

[v-shex-msft · Accepted solution]

Hi @Jcolvile,

You can simply use matrix to show the result which you wanted.

1. Create a new table with the distinct value.

Sample formula:
Table = DISTINCT('Original Table')

2. Create a matrix visual.
3. Drag invoice numbers to rows fields, invoice cost to value field.

 

Regards,
Xiaoxin Sheng