Possible newbie question about calculations in visuals

[Shaunes001]

I'm having a bit of a frustrating problem with a visual in PowerBi. 

This is one of our cases, usually charged hours and time costs are filled but this simple case only has Costs.

I'm trying to get Net Profit to show it's actual total rather than the total subtracted from Amount Billed as we can have multiple cases together and we're measuring profitability.

The formula should be simple: 

[Amount Billed]-[Costs]

I've tried calculate, sum, I just can't get the total to actually show the total.

Any ideas? It's probably really simple and I'm missing something.

Many thanks in advance!

[Anonymous]

Hi @Shaunes001 

Please try this:

Here I create a set of sample:

Table:

Then add a measure:

MEASURE =
SUMX ( 'Table', [Amount Billed] - [Costs] )

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Shaunes001 · Accepted solution]

I managed to figure it out. I merged and the columns from the different sources in to the same table. However, I then had to clean up duplicates of values using this:

Measure = CALCULATE(MAX(Table[column]))

I then used that in my Visualization for each column that had duplicates in a Case.

I've also used:

Measure = SUMX(Table,Table[Column]) for values that need to be added together, like Costs.

(This is based on Timesheets per person per case, split by activity that also need to account for different consultants having different hourly rates, and expenses).

Got it working nicely now!

[Shaunes001]

Hi all,

Just to clear some details up. This is all in a visual, the details of each column is coming from different queries, for example amount billed comes from Sage, Costs are from a SharePoint list and the Case ID is from CRM. We use the Case ID as a common lynch pin data between each.

The calculation itself works perfectly fine and does everything it needs to do, but the total at the bottom shows the total of the calcualtion, not the calculation of the column like any other summary. 

[dharmendars007]

Hello @Shaunes001 , 

You can try the below measure..

If(Hasonefilter(CaseID), 
                                    [Amount Billed]-[Costs], 

                                    Sumx(Values(CaseID),  
                                                                [Amount Billed]-[Costs]))

If you find this helpful , please mark it as solution and Your Kudos are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[Shaunes001]

Hi Dharmendar,

I tried the formula you suggested but sadly I have the same result:

The total is still showing the calculation and not the total for the column

[dharmendars007]

Hello @Shaunes001 ,

Sorry..Please try the below measure..

If(Hasonefilter(CaseID), 
                                    [Amount Billed]-[Costs], 

                                    Sumx(Values(CaseID),  
                                                                [Net Profit]))

If you find this helpful , please mark it as solution and Your Kudos are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[Shaunes001]

Sorry Dharmendar, I seem to be getting the same result. As mentioned, this is purely a visual and not a table, each column is coming from different sources. The calculation works as intended but the total is not correct.

[Gabry]

Please share some more details, like how is build amount billed measure