Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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?
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |