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.
I'm pulling all my hairs out for this..
I have a static column value and I need to create a new measure to sum it.
Sample.
A2 is (=A)
B2 is (=B1 + A2) and so on
I know this looks easy but I'm not sure how hard to write the DAX function measure.
Measure = VAR A = CALCULATE([Total],FILTER(Data,Data[Column1] = "ABC" && Data[Month] = "January")) VAR B= CALCULATE([Total],FILTER(Data,Data[Column1] = "ABC" && Data[Month] = "February")) VAR C = CALCULATE([Total],FILTER(Data,Data[Column1] = "ABC" && Data[Month] = "March")) RETURN CALCULATE(ABS(A),(B + A),(C + B)
I created this but I'm getting The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.
Solved! Go to Solution.
@v-yulgu-msftI figured it out. Thank you for looking into this.
I turned the Month Name into Month number and changed the new calculated type to decimal/number.
Month Num = IF(Table1[Month] = "January","1", IF(Table1[Month] = "February","2", IF(Table1[Month] = "March","3", IF(Table1[Month] = "April","4", IF(Table1[Month] = "May","5", IF(Table1[Month] = "June","6", IF(Table1[Month] = "July","7", IF(Table1[Month] = "August","8", IF(Table1[Month] = "September","9", IF(Table1[Month] = "October","10", IF(Table1[Month] = "November","11", IF(Table1[Month] = "December","12"))))))))))))
Then, I created a measure
Cumulative = CALCULATE([Total],FILTER(ALLSELECTED(Table1),Table1[Month Num] <= MAX(Table1[Month Num])))
I changed ALL to ALLSELECTED otherwise the value will be static.
Hi @Stuznet,
Please show us some sample data of 'Data' table. And what is the formula of [Total]? Besides, show us your desired output in Power BI.
How to Get Your Question Answered Quickly
Best regards,
Yuliana Gu
@v-yulgu-msftI figured it out. Thank you for looking into this.
I turned the Month Name into Month number and changed the new calculated type to decimal/number.
Month Num = IF(Table1[Month] = "January","1", IF(Table1[Month] = "February","2", IF(Table1[Month] = "March","3", IF(Table1[Month] = "April","4", IF(Table1[Month] = "May","5", IF(Table1[Month] = "June","6", IF(Table1[Month] = "July","7", IF(Table1[Month] = "August","8", IF(Table1[Month] = "September","9", IF(Table1[Month] = "October","10", IF(Table1[Month] = "November","11", IF(Table1[Month] = "December","12"))))))))))))
Then, I created a measure
Cumulative = CALCULATE([Total],FILTER(ALLSELECTED(Table1),Table1[Month Num] <= MAX(Table1[Month Num])))
I changed ALL to ALLSELECTED otherwise the value will be static.
Thanks, it is almost can't get the results. AllSelected (Table) Not AllSelected (Table.Column)
@v-yulgu-msfthere is the dataset. The total is the Value. My desired output is create a Cumulative Sum field that adds up the "Value"
Month | Value | Cumulative |
January | 63 | 63 |
February | 55 | 118 |
March | 30 | 148 |
April | 33 | 181 |
May | 50 | 231 |
June | 60 | 291 |
July | 69 | 360 |
August | 64 | 424 |
September | 60 | 484 |
October | 15 | 499 |
November | 14 | 513 |
December | 13 | 526 |
526 | 3838 |
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 |
---|---|
111 | |
101 | |
76 | |
64 | |
63 |
User | Count |
---|---|
141 | |
105 | |
101 | |
80 | |
67 |