Forum Discussion
New column with show only one classifier
Hello,
I am somewhat new in Power BI and I have been getting along with a few tutorials but I am unable to find the answer for this topic.
My data looks somewhat like this:
| Project | Required Heads | HiredHeads | TotReqHeadsbyProject | TotHeadsHiredbyProject | Category |
| Red | 12.00 | 14.63 | 15.00 | 14.63 | Under |
| Red | 3.00 | 0.00 | 15.00 | 14.63 | |
| Blue | 10.00 | 7.80 | 30.00 | 31.45 | Over |
| Blue | 10.00 | 13.65 | 30.00 | 31.45 | |
| Blue | 10.00 | 10.00 | 30.00 | 31.45 | |
| Green | 22.00 | 25.00 | 36.00 | 40.70 | Over |
| Green | 14.00 | 15.70 | 36.00 | 40.70 | |
| Yellow | 10.00 | 16.88 | 10.00 | 16.88 | Over |
The blue columns I was able to get by using SUMX, FILTER & Earlier, but I want to get the red column which shows only once the category for each project, if the total heads hired is over the total requested. E.g. the red category which has 14.83 heads hired and it needs 15, it would show Under.
Could you please help me out?
Anonymous ,
Suppose the [TotReqHeadsbyProject] and [TotHeadsHiredbyProject] are both calculate columns, you only need to add an index column and create a calculate column using dax below:
Category = IF('Table'[Index] = CALCULATE(MIN('Table'[Index]), ALLEXCEPT('Table', 'Table'[Project])), IF('Table'[TotReqHeadsbyProject] > 'Table'[TotHeadsHiredbyProject], "Under", "Over"))Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Tahreem24Super User
Hi,
You can give a try to the below measure:
MEASURE = IF(SUM(TOTAL HEAD) >SUM(TOTAL REQUIRED), SUM(TOTAL REQUIRED))
Please give Kudos to this effort and mark it as a solution if it helps!- Tahreem24
Hi Anonymous ,
Please mark it as a solution if my answer meets your requirement so that it may help others.
Thanks!
v-yuta-msftCommunity Support
Anonymous ,
Suppose the [TotReqHeadsbyProject] and [TotHeadsHiredbyProject] are both calculate columns, you only need to add an index column and create a calculate column using dax below:
Category = IF('Table'[Index] = CALCULATE(MIN('Table'[Index]), ALLEXCEPT('Table', 'Table'[Project])), IF('Table'[TotReqHeadsbyProject] > 'Table'[TotHeadsHiredbyProject], "Under", "Over"))Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.