Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
HSobral
Employee
Employee

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

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:

Picture3.png

 

 

 

 

 

 

 

 

 

 

 

 

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

3 REPLIES 3
HSobral
Employee
Employee

UP

 

Greg_Deckler
Super User
Super User

@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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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?

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.