Parse a column and then make a sum with other columns values when encountering last non blank value

[Anonymous]

I want to parse a sorted column, and then when I encounter the last non blank value from this column, take this row and make a sum with other columns values, from the same table. Is there any way to do that ?

Doing LASTNONBLANK(TIME1) + LASTNONBLANK(TIME2) + LASTNONBLANK(TIME3) would not work in my case since one my column does not have blank values.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try the following methods and check if they can solve your problem:

1.Create the simple table.

2.Create the new measure to calculate the last non blank value from the sales column.

LastNonBlankSales = CALCULATE(MAX('Table'[Sales]), FILTER(ALL('Table'[Sales]), NOT(ISBLANK('Table'[Sales]))))

3.Create the measure to make a sum with other column values.

TotalSum = [LastNonBlankSales] 
            + CALCULATE(MAX('Table'[Time 1]), FILTER(ALL('Table'[Time 1]), NOT(ISBLANK('Table'[Time 1])))) 
            + CALCULATE(MAX('Table'[Time 2]), FILTER(ALL('Table'[Time 2]), NOT(ISBLANK('Table'[Time 2])))) 
            + CALCULATE(MAX('Table'[Time 3]), FILTER(ALL('Table'[Time 3]), NOT(ISBLANK('Table'[Time 3]))))

4.Drag the measure into the card visual. The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try the following methods and check if they can solve your problem:

1.Create the simple table.

2.Create the new measure to calculate the last non blank value from the sales column.

LastNonBlankSales = CALCULATE(MAX('Table'[Sales]), FILTER(ALL('Table'[Sales]), NOT(ISBLANK('Table'[Sales]))))

3.Create the measure to make a sum with other column values.

TotalSum = [LastNonBlankSales] 
            + CALCULATE(MAX('Table'[Time 1]), FILTER(ALL('Table'[Time 1]), NOT(ISBLANK('Table'[Time 1])))) 
            + CALCULATE(MAX('Table'[Time 2]), FILTER(ALL('Table'[Time 2]), NOT(ISBLANK('Table'[Time 2])))) 
            + CALCULATE(MAX('Table'[Time 3]), FILTER(ALL('Table'[Time 3]), NOT(ISBLANK('Table'[Time 3]))))

4.Drag the measure into the card visual. The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi  @Anonymous,

Thanks for your response. My post was unclear but I wanted to take values from other columns in the same row that Max(Sales) in your example so I would need the total sum to be 86.

I ended up doing more or less the same than you though, filtering on the max. 

Best regards

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...