Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
User | Count |
---|---|
99 | |
87 | |
80 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |