Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi guys,
Above is the formula I created in Excel. Row 4 is what I'm trying to create in Power BI, but I'm not sure how do I achieve that.
Here is the Dax function I wrote but I'm not getting the correct value in November and December
Measure = CALCULATE([Total]),FILTER(ALLSELECTED(Table1),Table1[Month Num] <= Max(Table1[Month Num])))
Can someone please help me?
Thank you
Solved! Go to Solution.
Hi @Stuznet
Row3 = VAR Row1Sum = CALCULATE(DIVIDE([Total];2);FILTER(ALLSELECTED(Data);Data[Month Num] <= MAX(Data[Month Num]) && Data[Month Num] +1 >= MONTH(TODAY()))) VAR Row2Sum = CALCULATE(DIVIDE([Total];2);FILTER(ALLSELECTED(Data);Data[Month Num] <= MAX(Data[Month Num]) && Data[Month Num] +2 <= MONTH(TODAY()) && Data[ROW] <> BLANK())) RETURN Row1Sum + Row2Sum
Hi,
What is actually there in the first column. Are they years by any chance? Can you share your PBI file with dummy figures?
Here is the dummy report Cumulative Sum of 2 Rows, it is exactly what I'm trying to achieve on my active report.
Row 3 accumulating from Row 2. IF November and December <> BLANK(), I want it to accumulate from Row1 starting from October to December.
Note: Row 3 November to December number is the wrong result.
Thank you so you so much for looking into this.
Hi @Stuznet
Row3 = VAR Row1Sum = CALCULATE(DIVIDE([Total];2);FILTER(ALLSELECTED(Data);Data[Month Num] <= MAX(Data[Month Num]) && Data[Month Num] +1 >= MONTH(TODAY()))) VAR Row2Sum = CALCULATE(DIVIDE([Total];2);FILTER(ALLSELECTED(Data);Data[Month Num] <= MAX(Data[Month Num]) && Data[Month Num] +2 <= MONTH(TODAY()) && Data[ROW] <> BLANK())) RETURN Row1Sum + Row2Sum
I could not thank you-you enough, I could have never thought of that approach.
Thank you again 🙂
Hi,
Sorry i cannot help with this one.
Hi @Stuznet,
maybe this helps.
Measure = IF(HASONEFILTER(Table1[Row]);SUM(Table1[Value]);CALCULATE(SUM(Table1[Value]);FILTER(ALLSELECTED(Table1);Table1[Month Num] <= Max(Table1[Month Num]))))
Best regards,
Marcus
@mwegeber,
Thank you for providing your solution, that’s the correct value I’m looking for but I forgot to mention that in power bi, I created row 2 and 3 with a separate measures
Row 2 = VAR abc = CALCULATE( [total] / 1000000 RETURN IF(MAX( Table [Month Num] + 1 >= MONTH(TODAY()), abc , BLANK())
Row3 = VAR def = CALCULATE( [total], Filter( Table, Table[ Date] <> BLANK())) / (1000000) RETURN IF(MAX( Table [Month Num] + 2 >= MONTH(TODAY()), def , BLANK()
When I plugged in the measure in your solution I’m not getting the result
Measure = IF(HASONEFILTER( Table[Row]), CALCULATE( [Row2] + [Row3] , FILTER( ALLSELECTED( Table) , Table[Month Num] <= MAX(Table[Month Num]))))
Could you please advise? Thank you
Hi @Stuznet
Measure = CALCULATE([Row2]+[Row3],FILTER(ALLSELECTED(Table1),Table1[Month Num] <= Max(Table1[Month Num])))
I wish I can share the data, but my work computer is pretty locked down. I did try the 2nd functions you provided, I'm not getting the correct results.
Is there a way I can write a Cumulative with Conditional?
For Example,
Measure = If [Row 3] <> BLANK(), Sum [Row 2] that is not BLANK
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |