Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have a table containing:
the same material has the same value.
I want to calculate the sum of values groupped by materials, in other words avoiding summing twice the same material.
On top of that, I would like to display this measure in a chart at a constant.
I wrote this formula, but it does not aggregate correctly when selecting multiple materials.
M1 = CALCULATE(SUMX(ALLSELECTED(Data[Material]),SUM(Data[Stock Target])/COUNT(Data[Material])),ALL(Data[Date])
thanks
Solved! Go to Solution.
Got it!
SUMX(ALLSELECTED(Data[Material]),CALCULATE(sum(Data[Stock Target]),ALL(Data[Date]))/CALCULATE(COUNT(Data[Material]),ALL(Data[Date])))
Got it!
SUMX(ALLSELECTED(Data[Material]),CALCULATE(sum(Data[Stock Target]),ALL(Data[Date]))/CALCULATE(COUNT(Data[Material]),ALL(Data[Date])))
hi @MagikJukas
unable to follow, you mentioned:
"same material has the same value"
and
"avoiding summing twice the same material"
so why bother sum at all? Could you explain your expectation further?
I want to group by material and then sum the total.
It is like counting and count distinct, I do not want to count more than once the same material.
Hopefully it is more clear now.
User | Count |
---|---|
50 | |
24 | |
18 | |
17 | |
14 |
User | Count |
---|---|
91 | |
86 | |
39 | |
25 | |
20 |