change Total value to a calculated value for 1 column only

[kfschaefer]

Is there a way to change a single column's Total to calculate something different(manual calculation) then the remaining total values that are summarized?

the column inquestion is a calculated field and I was hoping to carry that calculation to be displayed in the Total Row in my visualization.  The calculation should be "NI $ per group" Totals divided by "Total Revenue" Total value.  and return a percentage.

[Sean]

@kfschaefer

Look at how the numbers in the Column are LEFT justified!!!

Look how the rest are RIGHT justified.

This is a Visual Clue => check the DATA TYPE

[kfschaefer]

I know I did not want to summarize the percentage, hence I want to display the results of a calculate field in the Total Row.

I want to use the following:NI% = DIVIDE(PctNITotal[totalNI$],PctNITotal[totalRevenue]) but in the Total Row only and let it change based on slicer selection.

 

[Sean]

If you have the right Measures the Total % what you are looking for 6.7% will show up in the Total Row!

It will not be a sum of the whole column? Have you worked with Pivot Tables before?

[kfschaefer]

What wopuld the measure look like and would that be the same for the enitre column and also would I set the total to summarize or what?

[kfschaefer]

I need to have the total value for the following calculation:

DIVIDE(PctNITotal[totalNI$],PctNITotal[totalRevenu​e])  = 6.6% for 2015 or a running calcuation the recalcs when slicers is selected  See picture in last post, the Measure was just so I can illustrated the value I wanted in the total row.

[Sean]

@kfschaefer 

You CANNOT put a different Measure in the Table just in the Total Row under NI% per Group where you want that 6.7% to show up

You should not have columns NI $ per Group and NI% per Group but rather just NI $ and NI % (with no filters)

The Group Name Column will filter the results

And then you you will get the Totals you desire on the bottom

[kfschaefer]

here is my effort without success:

[Sean]

@kfschaefer to get NI% you divide NI by Revenue

NI% = DIVIDE ( SUM(powerBIData[NI $], SUM(powerBIData[Total Revenue]) )

if you place this measure next to NI% by Group - for each Group you should get the same result AND the Total should be your 6.7%

[kfschaefer]

I am getting too many arugments error:

[Sean · Accepted solution]

@kfschaefer

Ok we are going to end up creating those Measures...

Bear in mind I don't know what names Measures and Columns you have so you may have to change the names of the Measures

Net Income $ = SUM(powerBIData[NI $])

Overall Revenue = SUM(powerBIData[Total Revenue])

Net Income % = DIVIDE([Net Income $], [Overall Revenue])

Now place Net Income $ and Net Income % in your table and you'll see them broken down by group and correct totals on the bottom.

EDIT: I think you have an extra ) in the previous formula that was giving you error.

[kfschaefer]

nevermind, I figure that out and thank you so much spending your Saturday helping me.

[Sean]

[kfschaefer]

ok we are getting close, how do I change the percentage to display a percent in a measure?

[kfschaefer]

Those are the labels given to me by my client, still confused,  If Sum the % it gives me the extremely high value.  What do I used instead of Sum?

What would the Measure look like, I never really used them before.  and how will that effect the total value? 

[Sean]

So the data they gave you is already calculated for each Group?

all the numbers in the NI$ per Group and NI% per Group columns are given to you - you don't calculate them

Post a picture from the Report View of the Fields from the table where these 2 columns are

[Sean]

@kfschaefer

Seems to me you've created extra unnecessary Measures?

What is you formula for these 2 columns => NI $ per Group AND  NI% Per Group?