Total row with wrong results in SQL Analysis Services database live connection

[HSobral]

Hello all,

I'm facing a huge problem while trying to bring the correct values to a total row in a table. 

I'm live connected (Direct Query) to a SQL Analysis Services database and this limit my options to solve this issue. I cannot add new columns, cannot add new tables and this is why I've been looking for an alternative solution to this problem.

I've added a table containing several columns, and the totals won't show the correct information. I know that PBI have some trouble getting the context of the data, but I'm struggling to make PBI understand it.

I'm going to use an Excel table as an example, but imagine the following situation happening on Power BI:

The column "Value RAW" comes straight from the database as a measure. The column "Value (Non Negatives)" is a calculated measure as this:

Value (Non Negatives) =

    CALCULATE(

    SUMX(table_name

    ,IF([Value RAW]>0

    ,[Value RAW]]

    ,BLANK())

    )  

)

The row "Total" is what PBI is delivering to me, I'd like that this row showed the same values as the "Expected Value" row. 

The main goal is to show only the sum of the positive values in the Total Row, or even the correct value.

One important information is that most of the data is stored in the database as "measures" and not as columns.

Would you help me? I really appreciate any help.

Thank you in advance

Henrique

[HSobral]

UP

[Greg_Deckler]

@HSobral First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[HSobral]

Hello, Greg

Thank you for your response.

I've tried all the solutions you posted, but unfortunately they didn't work on this case.

As I kept trying to sum the values using alternative columns (that are in the same table view in my dashboard) in the summarize function, I got different values for the new measure. I got pretty close to the desired outcome, but not exactly with the same numbers.

One of the ways you listed worked in another dashboard I had the same issue (this one is not live connected to the SQL Analysis Services).

Do you think this is related to the Live Connection?