Creating Calculated Column Total for Table Visualizations

[JC_24]

Hi everyone,

I'm trying to edit the column totals for a table visualization so that they aren't all just a sum of the data. Ideally I'd like to make a calculation of two different fields and add that as the column total. Would something like this be possible? 

Let me know if you need more information to understand the situation or want clarification on something. 

Thanks!!

[Anonymous]

Hi @JC_24 ,

You can write totals by measure. Please refer to the example below.

Based on your description, I have created these data.

Create measure. This measure displays the data of Field1 in Category and the sum of the data of Field1 and Field2 in Total.

MEASURE = 
IF (
    HASONEVALUE ( 'Table'[Category] ),
    SUM ( 'Table'[Field1] ),
    SUM ( 'Table'[Field1] ) + SUM ( 'Table'[Field2] )
)

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[JC_24]

Hello! Thank you so much for your help!

I think I might be trying to use the wrong visualization for this goal. I understand the method of creating a measure / calculated column for the calculation I need but how would I go about putting this calculation into a visualization? Currently I'm using the "Table" visualization from the "Build Visualization" seen here and then turning on the "Totals" for the visualization : 

Would I be able to put the new measure into the totals bar (seen with the X in my screenshot) of my "Table" visualization? If not what would be the best method for achieving this?

Note: The actual columns that are displayed in the table I don't want to calculate, simply display the data that was loaded in already. I only want to display a calculation for their totals bar. 

[Anonymous · Accepted solution]

Hi @JC_24 ,

A slight change to the previous expression is sufficient.

MEASURE =
IF (
HASONEVALUE ( 'Table'[Category] ),
BLANK(),
SUM ( 'Table'[Field1] ) + SUM ( 'Table'[Field2] )
)

The effect of the HASONEVALUE function is to return TRUE when the context of the columnName has been filtered to only one non-duplicate value; otherwise it is FALSE.
More details can be found in the documentation:

HASONEVALUE function (DAX) - DAX | Microsoft Learn

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[Kaviraj11]

Can you provide an example as well? However, from you given statement, it should be able to use.

[JC_24]

Hello! I've provided an example in a different reply just now, if possible please refer to my other reply for more clarification on what I'm trying to achieve. Thank you in advance for your help!

[Kaviraj11]

Hi,

You can create a custom measure that performs the calculation you need. Here is an example:

TotalCalculation =
SUMX(
TableName,
TableName[Field1] - TableName[Field2]
)

If you need more complex calculations for the totals, you might need to use more advanced DAX functions like SUMMARIZE, CALCULATE, or AVERAGEX

[JC_24]

Thank you so much for the reply! Would I be able to use the custom measure in the table visualization's column totals? For example I'm looking at the "Format your Visuals" tab of Visualizations and don't see an option to manually input what the total for each column would be. Let me know if that's not clear and I'll be happy to clarify.