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 there,
I'm stuck at the moment, so I hope someone can give me some light at this stage regarding this subject.
I've been trying to normalize my data, using the following example (extracted from a similar problem)
Value First Date = /* Optional check: Only return Value First Date up to the max date that actually appears in the fact table */ IF ( MIN ( 'Calendar'[Date] ) <= CALCULATE ( MAX ( Data[Date] ), ALL ( Data ) ), CALCULATE ( [Value], CALCULATETABLE ( FIRSTDATE ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) ) ) )
Value Normalized = DIVIDE ( [Value], [Value First Date] ) * 100
This example works great/perfect if I want to normalize my data based on the 1st day of the 1st month.
This example will grab "200" from the following table, but what I need here is to get "900" and do exactly the same as the rest of the example code.
DataDate Value
1-01-2017 200
3-01-2017 400
7-01-2017 300
My Goal Here is to do exactly the same, but using the sum of all values in the 1st month, instead of the 1st value (firstday) only.
The calculation is done then by dividing each of the values of following months by the total value of the first month(900), and multiplying by 100.
So, I'm starting my DAX now, so apologies if this is too easy.
Thanks in advance.
Peter
Solved! Go to Solution.
Hi @v-chuncz-msft, thanks for coming back to me.
It´s not easy since I have a lot of confidential data and several metrics.
Anyway, hopefully, I managed to find a solution for this, the following way:
1-
IndexR2 = CALCULATE ( sum(Facts[Sales_U]),
CALCULATETABLE ( DATESBETWEEN('Date'[DateFullName],STARTOFMONTH('Date'[DateFullName]),ENDOFMONTH(STARTOFMONTH('Date'[DateFullName]))), ALLSELECTED ( 'Date' ) ) )
2-
IndexR2N = DIVIDE ( Calculate(Sum(Facts[Sales_U])), [IndexR2] ) * 100
and that's it.
Thanks anyway, and sorry case I was not clear and able to share the .pbix
Peter
The example above is not very clear. It would be better if you could share us your simplified .pbix file.
Hi @v-chuncz-msft, thanks for coming back to me.
It´s not easy since I have a lot of confidential data and several metrics.
Anyway, hopefully, I managed to find a solution for this, the following way:
1-
IndexR2 = CALCULATE ( sum(Facts[Sales_U]),
CALCULATETABLE ( DATESBETWEEN('Date'[DateFullName],STARTOFMONTH('Date'[DateFullName]),ENDOFMONTH(STARTOFMONTH('Date'[DateFullName]))), ALLSELECTED ( 'Date' ) ) )
2-
IndexR2N = DIVIDE ( Calculate(Sum(Facts[Sales_U])), [IndexR2] ) * 100
and that's it.
Thanks anyway, and sorry case I was not clear and able to share the .pbix
Peter
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 |
---|---|
114 | |
100 | |
78 | |
75 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |