Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have a table with multiple columns but i want to performa calculation on 2 columns. here is the sample data.
I want to find the last non zero value in column A and then get the sum of all the values in column B starting from Last non-zero value of column A till all the rows with 0 values in column A. In short, calculation needs to be performed on highlighted area only.
Please let me know how can i do that. Appreciate any help.
Thanks in Advance
Solved! Go to Solution.
@tahira_ali_28
Create the following measure:
Measure =
VAR __LastNonBlank = LASTNONBLANKVALUE( Table1[A] , MAX(Table1[Index] ))
RETURN
CALCULATE(
SUM(Table1[B]),
Table1[Index] >= __LastNonBlank
)
File attached below my signature
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @Fowmy ,
The above provided solution is not working for this data set. would you please help me with that?
what i understand after testing the above measure that MAX fucntion is not working properly, its not picking up the correct last non-zero value in a column.
Your help in this matter is highly appreciated. Thanks in Advance!
@tahira_ali_28
Create the following measure:
Measure =
VAR __LastNonBlank = LASTNONBLANKVALUE( Table1[A] , MAX(Table1[Index] ))
RETURN
CALCULATE(
SUM(Table1[B]),
Table1[Index] >= __LastNonBlank
)
File attached below my signature
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks Fowmy, its fixed my issue.
User | Count |
---|---|
100 | |
87 | |
81 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |