Help with calculating a new column

[Anonymous]

Hi,

I need to show a trend line for the Total and also for the 85% Total, but the value for the Total doesn’t exist in the source data.  To make it more complicated, the 85% Total exists but it’s in a column that also contains Team Members.

I’ve tried creating a calculated column that looked something like this:

Total = IF(CONTAINS(Table 1, Table 1[Team], “85% Total”) CALCULATE(Table 1 [Cost])/.85)

I also tried using CALCULATE & ALLEXCEPT but haven’t had any success.

It’s a straight forward problem in Excel but PBI requires a different twist that I can’t quite put my finger on.

The table below shows the existing data in BLACK and two new columns in RED to hold the Total and 85% Total values.

Maybe there's a better approach or possibly some help withi my syntax?  Any input is appreciated.

[Phil_Seamark]

HI @Anonymous

So I take it you are trying to generate the numbers in the red box, but only have access to data in the first two column to generate the numbers.   It's not clear what is used to generate the top value of 200,000 and then the 176,470

[Anonymous]

Hi Phil,

Sorry.  I should've been clearer with my question. I've recreated the drawing to more closely match the data.  I need to calculate the Capacity.  Unforutnatley, the Capacity numbers aren't part of the source data but the 85% of Capacity is in the source data so  I'm using that to get the full capacity.  To allow me to create two distinct lines in a chart, I believe I'll need to break out these two values into two separate columns (one for Capacity and one for 85%).

Does that help at all or did I make it more confusing?

[Phil_Seamark · Accepted solution]

I think I understand.

Please try these two calculated columns.  For clarity, I have attached a PBIX file. 

85% Capacity = 
    IF(
        'Table1'[Team]="85% Capacity",
        'Table1'[Cost]
        )

and

Capacity = 
    IF(
        'Table1'[Team]="Capacity", 
        SUM('Table1'[85% Capacity])/.85
        )

[Anonymous]

Phil,

That worked great.  Thanks for the help!