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.
Hi Power BI Community
I have a transaction table below.
I need to create a report filtered by SALE DATE, and PRODUCT ID.
In the report i want to show:
1- The number of transactions including these products
2- The total value of those transactions (including products not filtered)
1 is easy. I just need to use a distinctcount of the ticket ids. Then i apply any filter and it works fine
But 2 is complicated because i need to calculate The total value of each transaction (i.e. the SUM of SALE AMOUNT per transaction) then apply the filters (SALE DATE and PRODUCT ID) and SUM the Total value of alll filtered transactions.
TICKET ID | SALE_DATE | LINE NB | SHOP ID | PRODUCT ID | SALE QUANTITY | SALE AMOUNT |
32605236 | 22/04/2018 00:00 | 2 | 2 | 48191 | 1 | 52 |
32605236 | 22/04/2018 00:00 | 4 | 2 | 47147 | 1 | 25,2 |
32605236 | 22/04/2018 00:00 | 5 | 2 | 53495 | 1 | 0 |
32605237 | 22/04/2018 00:00 | 2 | 2 | 50234 | 1 | 0 |
32605237 | 22/04/2018 00:00 | 4 | 2 | 49004 | 1 | 5,95 |
32605237 | 22/04/2018 00:00 | 5 | 2 | 47318 | 1 | 8 |
32605238 | 22/04/2018 00:00 | 2 | 2 | 47360 | 1 | 3,33 |
32605238 | 22/04/2018 00:00 | 3 | 2 | 49311 | 1 | 3,33 |
32605238 | 22/04/2018 00:00 | 4 | 2 | 52836 | 1 | 3,34 |
32605238 | 22/04/2018 00:00 | 6 | 2 | 53495 | 1 | 0 |
32605238 | 22/04/2018 00:00 | 7 | 2 | 53811 | 1 | 0 |
32605238 | 22/04/2018 00:00 | 8 | 2 | 47634 | 1 | 10 |
32605239 | 22/04/2018 00:00 | 2 | 2 | 49189 | 1 | 9 |
32605239 | 22/04/2018 00:00 | 3 | 2 | 52139 | 1 | 9 |
32605239 | 22/04/2018 00:00 | 4 | 2 | 8998 | 1 | 12 |
32605239 | 22/04/2018 00:00 | 5 | 2 | 9014 | 1 | 10 |
32605239 | 22/04/2018 00:00 | 6 | 2 | 9019 | 1 | 10 |
32605240 | 22/04/2018 00:00 | 2 | 2 | 53728 | 1 | 0 |
32605240 | 22/04/2018 00:00 | 3 | 2 | 54398 | 1 | 0 |
32605240 | 22/04/2018 00:00 | 4 | 2 | 52366 | 2 | 8 |
32605240 | 22/04/2018 00:00 | 5 | 2 | 53812 | 3 | 14,85 |
32605241 | 22/04/2018 00:00 | 1 | 2 | 4099 | 1 | 12 |
32605241 | 22/04/2018 00:00 | 2 | 2 | 46081 | 1 | 45 |
32605242 | 22/04/2018 00:00 | 1 | 2 | 19018 | 2 | 26 |
Solved! Go to Solution.
I actually found another solution.
I have 2 tables : 1 sale detail and 1 sale header.
I found a way to create a measure in sale detail that points to the sale header using USERRELATIONSHIP.
It works.
Thanks
F
Hi @fabienrolland,
Believe that the you want the calculation made by Quantiy * Sales amount
Ue something like this measure:
Measure = SUMX(Sales;Sales[SALE AMOUNT]*Sales[SALE QUANTITY])
Regards
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi MFelix.
Thanks but that is not what i'm looking for.
What you propose is a measure to calculate the total sales amount which is great but the problem is i need to filter product ids and calculate the total value of the transactions that include these product ids.
Example:
Let's take 2 tickets 32605236 and 32605237 (the first 2 in my list)
If i use your formula, which allows me to calculate the Sale Amount for each line. I get this.
Now, what i need is to calculate the total for each ticket, so:
32605236 = 77.2
32605237 = 13.95
Now to complicate things even more, in my report, i need a measure that shows those totals even when i filter on the product for ex: In this case let's say i filter on product 48191, if i sum your formula, i will get 52, when what i want to show is 77.2.
In other words, i want to filter on transactions containing 1 product and show the total value of the transactions containing this product.
TICKET ID | SALE_DATE | LINE NB | SHOP ID | PRODUCT ID | SALE QUANTITY | SALE AMOUNT | Measure |
32605236 | 22/04/2018 00:00 | 2 | 2 | 48191 | 1 | 52 | 52 |
32605236 | 22/04/2018 00:00 | 4 | 2 | 47147 | 1 | 25.2 | 25.2 |
32605236 | 22/04/2018 00:00 | 5 | 2 | 53495 | 1 | 0 | 0 |
32605237 | 22/04/2018 00:00 | 2 | 2 | 50234 | 1 | 0 | 0 |
32605237 | 22/04/2018 00:00 | 4 | 2 | 49004 | 1 | 5.95 | 5.95 |
32605237 | 22/04/2018 00:00 | 5 | 2 | 47318 | 1 | 8 | 8 |
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI would like to create a table filtered by date (SALE_DATE) and product (PRODUCT ID)
The table would include Store ID in lines and in columns :
- Nb of transactions (that's DISTINCTCOUNT(TICKET ID)
- Total amount of products filtered (SUMX(SALE AMOUNT * SALE QUANTITY)
- Total value of transactions that include the filtered product (the one measure i need help on)
Thanks a million for your help
@fabienrolland Can you share a snapshot of the output ? Especially for the last measure
I actually found another solution.
I have 2 tables : 1 sale detail and 1 sale header.
I found a way to create a measure in sale detail that points to the sale header using USERRELATIONSHIP.
It works.
Thanks
F
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 |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |