Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Help with calculating a new column

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...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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's avatar
Anonymous
Not applicable
8 years ago

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's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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's avatar
    Anonymous
    Not applicable
    8 years ago

    Phil,

    That worked great.  Thanks for the help!