Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello every one,
I am posting this for the second time, since I had no help in the first time, any remark would be helpful.
So I am new to dax and I am writing this measure that calculates a weighted value (SUMPROD(volume, TS) divided bySUM(Volume)), but the problem is that TS values are not there every day, so I have to multiply the volume with TS avg of the previuos month, I see that my reasoning is right bu something dosen't seem to work.
Here is My measure :
The previous month measure:
Here is a piece of my data , as you can T/S is not there every time:
And here what I get when I show data as a table:
Any remark would be helpful.
thank you
Solved! Go to Solution.
Thank you for all your feedbacks.
Finally I found the solution, what I did is that I added a calculated column where I store the multiplication of Volume and T/S:
And then I used it with the formula as follows:
Thank alot for your efforts.
Hi @salim123
Please try
TS Ponderee (Madcap) =
VAR PreviousMonthTS = [previous_month_ts]
VAR Numerator =
SUMX (
'all years data',
IF (
'all years data'[T/S] <> BLANK (),
'all years data'[Vol L] * 'all years data'[T/S],
'all years data'[Vol L] * PreviousMonthTS
)
)
VAR Denominator =
SUMX ( 'all years data', 'all years data'[Vol L] )
RETURN
DIVIDE ( Numerator, Denominator )
Thank you for your feedback.
I tried the formula provided in your response, but it is still the same problem:
Yes and here is its formula:
@salim123
Please test the following
TS Ponderee (Madcap) =
SUMX ( 'all years data', IF ( 'all years data'[T/S] <> BLANK (), 1, 0 ) )
Here is what it outputs:
Thank you for all your feedbacks.
Finally I found the solution, what I did is that I added a calculated column where I store the multiplication of Volume and T/S:
And then I used it with the formula as follows:
Thank alot for your efforts.
User | Count |
---|---|
98 | |
90 | |
84 | |
71 | |
67 |
User | Count |
---|---|
114 | |
103 | |
100 | |
72 | |
64 |