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.
Hello,
I am fairly new to DAX so i am sure it would have been a common case but i am not able to find a solution.
Sample data for the problem :
I have three columns and i want the sum to based on distinct values in item and cost group.
For example in the above dataset i need cost 1,000 only once since it is for the same item but 2,000 to be counted twice as it is for different items while calculating sum.
I have used the following formula: CALCULATE(SUM(ITEM), GROUPBY( TABLE_NAME, ITEM, COST))
But this gives me sum of cost as 7,000
Any help would be appreciated.
Thanks in Advance!!
Solved! Go to Solution.
@Anonymous
You can try your measure this way, hope you have a similar number for each item.
Total =
SUMX(
SUMMARIZE( table5, Table5[ITEM], "A", MAX(Table5[COST])),
[A]
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thank you for sharing the measure you tried to use! This helped me with mine, as my data model looks like this:
Account ID | Product ID | Transaction Date | CountedRowsFromAnotherTable (linked by Product ID) |
ABC | Prod1 | 1/1/2020 | 100 |
ABC | Prod2 | 1/5/2020 | 50 |
DEF | Prod3 | 2/2/2020 | 20 |
What I wanted to get is a total of 150 for account, "ABC" for the year 2020, and it worked!
This measure works, thanks for the awesome solution.
@Anonymous , Try a measure like
sumx(Values(Table[item]) , calculate(max(Table[Cost])))
This is exactly what i needed today, I cant believe it was so simple. Thank you!
I was also facing same problem, and it worked for me,
Thankyou so much.
@Anonymous
You can try your measure this way, hope you have a similar number for each item.
Total =
SUMX(
SUMMARIZE( table5, Table5[ITEM], "A", MAX(Table5[COST])),
[A]
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |