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
        )

Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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'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!