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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
pborah
Continued Contributor
Continued Contributor

Why is column total in table visual not recognizing ABS function?

I have this table -

 

pborah_0-1717013194678.png Where Rqd  People (measure)= ABS(MIN(columnA)). And ColumnA has both positive and negative values. When I turn on Column Totals option in visual formatting, it is giving me the total taking into account the integer signs for each cell and then finally removing the integer sign at the total stage. That is to say- without the ABS function, the column total is -3, and with ABS applied, the visual is simply removing the - sign from that total. That is absurd! Isn't it supposed to give me total for what is visually represented in that table instead? 

 

1 REPLY 1
Transform121
Frequent Visitor

No it is not. Power BI respects the context with which you give it instructions.

 

It looks like Power BI is taking into consideration the ABS function with respect to the order of operations you have passed it in your measure.

 

You have passed it: 

 

Measure = ABS ( MIN ( [ColumnA] ) )

 

When the measure is placed into a matrix the measure is evalulated row by row as it individually filters its conditions to meet the existing columns. The total respects the context of the measure with relation to the column.

 

It will determine the minimum value available from [ColumnA]. Without seeing [ColumnA] data it would be an assumption that the minimum value in the column is -3. Therefore PowerBI is assessing the column with the following steps.

 

1) Measure = ABS ( MIN( [ColumnA] ) )
2) Measure = ABS ( -3)
3) Measure = 3

 

 

The column total is respecting the context in which you have declared values for the measure (which has come through as ABS(-3) and is therefore showing you the answer of 3).

 

What are you wanting to see for the Total? I am extrapolating that you are wanting to see something along the lines of 'the sum of all values if they were absolute instead of negative values'. If that is true; you will need to use either of the following formulas with a slight variance:

CALCULATE(SUM(ABS[ColumnA]), filter1, filter2, etc.)

or

SUMX(FILTER(TableFilter1, filter2, etc.), ABS[ColumnA])

 

These calculations will allow for the measure to assess row by row what the correct value is, and then the functionality of the Total line will aggregate the result to show effectively show you the sum of all the existing results.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.