Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi
I have a table with 17 columns. 1 of them is receipt_ID and other is sales_amount. I need to sum or group all the same receipt_id into 1 sales_amount.
For example:
receipt_ID | sales_amount |
11412 | 7 |
11412 | 4 |
11522 | 1 |
11522 | 8 |
I need to SUM all 11412 receipt_ID into total sales_amount which in my example would be 11 and same with 11522 which should be 9. I don't want to use query editors Group By function since I want to keep all the other columns aswell. Was thinking something in DAX.
Solved! Go to Solution.
@Anonymous , a simple measure with receipt id in visual should do
sum(Table[sales_amount])
or
calculate(sum(Table[sales_amount]) , allexcept(Table, Table[receipt_id]))
a new column
sumx(filter(Table, table[receipt_id] = earlier(Table[receipt_id])) ,[sales_amount])
Hi,
Not sure to get what you really need, but if it is just tohave the total per reciptID, just make a table and choose in drop down menu Sum instead of Do not summarize.
https://docs.microsoft.com/en-US/power-bi/create-reports/service-aggregates
Hope this helps
@Anonymous , a simple measure with receipt id in visual should do
sum(Table[sales_amount])
or
calculate(sum(Table[sales_amount]) , allexcept(Table, Table[receipt_id]))
a new column
sumx(filter(Table, table[receipt_id] = earlier(Table[receipt_id])) ,[sales_amount])
I tried with a measure now:
sum(Table[sales_amount])
and this seems to be working better but another problem is, I have 1 column with item quantity, to get the total sum I need to multiply sales_price with quantity.
Measure for my Sales Value:
Measure for my multiplication with quantity:
Thank you for the reply.
I tried using:
calculate(sum(Table[sales_amount]) , allexcept(Table, Table[receipt_id],Table[date]))
and all seemed to work but when I did some checking the numbers dont add up. ( made a new table and this shows all the receipt_id's not as 1.
How the heck the total is 709, also in filter i see in store level there should be 29 lines but my table only shows 20, why are the 9 hidden? If you add them all up from ( with quantity ) the total is 49,18 but PBI shows me total 709,92. Same result in the new column: calculate(sum(Table[sales_amount]) , allexcept(Table, Table[receipt_id],Table[date]))
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
107 | |
105 | |
89 | |
61 |
User | Count |
---|---|
169 | |
138 | |
135 | |
102 | |
86 |