Forum Discussion
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 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.
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 )
4 Replies
- Phil_Seamark
Microsoft Employee
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
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
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 )