Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear All,
I am trying to calculate how much revenue is gained through clients which buy a low annual quantity for specific type of products.
Enclosed I put 2 tables as a reference.
Steps I am trying to do but doesnt work:
Total revenue type good= CALCULATE(SUM(Transaction Table[amount]),FILTER(Transaction Table[type] = "Revenue"), FILTER(Type clients[type] = "Good"))
Client name | Date | type | Amount | qty | Itemcode |
A | 2019 | Revenue | 1000 | 1 | Item 1 |
B | 2019 | Revenue | 200 | 8 | Item 1 |
C | 2019 | Revenue | 300 | 8 | Item 1 |
D | 2019 | Revenue | 500 | 6 | Item 1 |
E | 2019 | Revenue | 800 | 1 | Item 1 |
F | 2019 | Revenue | 900 | 2 | Item 1 |
A | 2019 | Revenue | 500 | 6 | Item 2 |
B | 2019 | Revenue | 66 | 4 | Item 2 |
C | 2019 | Revenue | 500 | 2 | Item 2 |
D | 2019 | Revenue | 300 | 7 | Item 2 |
E | 2019 | Revenue | 400 | 10 | Item 2 |
F | 2019 | Revenue | 800 | 1 | Item 2 |
A | 2019 | Revenue | 500 | 6 | Item 2 |
B | 2019 | Revenue | 66 | 4 | Item 2 |
C | 2019 | Revenue | 500 | 2 | Item 2 |
D | 2019 | Revenue | -300 | 7 | Item 2 |
E | 2019 | Revenue | -400 | -5 | Item 2 |
F | 2019 | Revenue | 800 | 1 | Item 2 |
1 | 2019 | Costs | -50 | ||
2 | 2019 | Costs | -3 | ||
3 | 2019 | Costs | -500 | ||
4 | 2019 | Costs | -800 | ||
5 | 2019 | Costs | -9000 | ||
6 | 2019 | Costs | -10000 | ||
7 | 2019 | Costs | -5000 | ||
8 | 2019 | Costs | -400 |
Type client
Client name | Type |
A | Good |
B | Good |
C | Good |
D | Good |
E | Bad |
F | Bad |
Solved! Go to Solution.
Hi @replayyy ,
Try this
Measure = SUMX(FILTER(Revenue,RELATED('Client Category'[Type]) = "Good" && Revenue[qty] < 5),Revenue[Amount])
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
Hi @replayyy ,
Try this
Measure = SUMX(FILTER(Revenue,RELATED('Client Category'[Type]) = "Good" && Revenue[qty] < 5),Revenue[Amount])
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
@replayyy , use all expect and build this formula at the client level and use
https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/
Thnx Amitchandak, I have read the article and I am not sure how to incorporate the allexcept function. Care to elaborate a little?
Thank you!
User | Count |
---|---|
92 | |
85 | |
76 | |
66 | |
62 |
User | Count |
---|---|
110 | |
96 | |
95 | |
64 | |
57 |