Forum Discussion
Band Movement From and To in Power BI Table
Hi Guys
I have a data at employee level for 12 months along with their bands (Current month & Previous Month) and other attributes. Created 2 different metrics, 1.Promotion, 2.Demotion. Promotion = Pple who moved up to next band as compared to their previous month band. 2. Demotion - pple moved down to lower band.
Now i have to show this in a table by Band Group. Like below,
Band Group(Current month) Promotion Demotion
M1 5 3 (pple moved down from M1)
Mid 12 15 (pple moved down from Mid)
Low 25
The challenge in here is, Promotion numbers are of Employee's Current month Band.
But demotion should be shown as no. of pple who got demoted from M1 to Mid = 3 and that has to shown against M1. In other words, the demotion numbers should be shown against the previous month Band and promotion numbers at the current month band in 1 single table. The solution which we can think of is, create a separate demotion table and append it below the HC table in power query. But with the current set up i cannot work on that, as i have to recreate 100 plus measures which was already created in the base table (HC Table) and i cannot disturb the base table.
Kindly help to achieve this.
P.S I have tried filtering out Band decrease in a separate table and lookup/join Period and Current Month Band from HC base table to Period and Prev month Band. It works, but the problem is, if i have more filters like, by Business, by region, By country, By manager applied, this join doesnt work/the numbers dont reflect/refresh.
Attaching the sample Output here.
Hi narenstat ,
Actually am unable to add the PBIX file sharing you the dax measures.
Band Decrease Count = CALCULATE( COUNTROWS('YourTable'), 'YourTable'[Band Decrease] = 1 ) Band Increase Count = CALCULATE( COUNTROWS('YourTable'), 'YourTable'[Band Increase] = 1 ) Total Exits = SUM('YourTable'[Exits]) Total Headcount = COUNTROWS('YourTable')Regards,
Chaithanya.
11 Replies
v-kathullacCommunity Support
Hi narenstat ,
Thank you for reaching out to Microsoft Fabric Community Forum.can you Please provide sample data or sample pbix File to do some work around on your issue
Regards,
Chaithanya.
As am new to the forum, i dont have option to attach the sample files here. Attaching the screenshots here which might help.
Sample Data:
Output:
- v-kathullac
Hi narenstat ,
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Regards,
Chaithanya.
As i said, am unable to attach the sample data in this forum. Thats why sent the screenshot.
- v-kathullac
Hi narenstat ,
The data you provided in the image wasn't very clear, so I generated some random data myself and created an output similar to what you showed in the image.
I'm attaching the screenshot for your review.
Regards,
Chaithanya.
- v-kathullac
Hi narenstat ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,Chaithanya
- v-kathullac
Hi @narenstat ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,Chaithanya