Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
84 | |
39 | |
25 | |
21 |