Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Power BI community,
I am new to power BI and DAX therefore my skills are kinda of limited. I have to split a single row into multiple rows. let me illustrate my request :
Employee ID | month | salary | bonus |
1 | Annual | 12000 | 3000 |
The month cell is supposed to have 12 month in stead of annual and i am supposed to have the salary and the bonus split into month (so divided by 12). The final result I am expecting should look like this:
Employee ID | month | salary | bonus |
1 | january | 1000 | 250 |
1 | february | 1000 | 250 |
1 | march | 1000 | 250 |
1 | april | 1000 | 250 |
1 | may | 1000 | 250 |
1 | june | 1000 | 250 |
1 | july | 1000 | 250 |
1 | august | 1000 | 250 |
1 | september | 1000 | 250 |
1 | october | 1000 | 250 |
1 | november | 1000 | 250 |
1 | december | 1000 | 250 |
So I wanted to know if it is possible to do that directly in Power BI or i have to change everything in the excel sheet?
Thank you 🙂
Solved! Go to Solution.
@Anonymous Please try with below steps:
1. Create a new MonthName table from your existing DimDate table (if you don't have one, create a Date table)
_DimMonth = VALUES(_Date[Date].[Month])
2. Then, create a new table as below which will be your expected output
Test273Out = CROSSJOIN(SELECTCOLUMNS(Test273SplitRows,"EmployeeID",Test273SplitRows[EmployeeID],"Salary",Test273SplitRows[Salary]/12,"Bonus",Test273SplitRows[Bonus]/12),_DimMonth)
Proud to be a PBI Community Champion
@Anonymous Please try with below steps:
1. Create a new MonthName table from your existing DimDate table (if you don't have one, create a Date table)
_DimMonth = VALUES(_Date[Date].[Month])
2. Then, create a new table as below which will be your expected output
Test273Out = CROSSJOIN(SELECTCOLUMNS(Test273SplitRows,"EmployeeID",Test273SplitRows[EmployeeID],"Salary",Test273SplitRows[Salary]/12,"Bonus",Test273SplitRows[Bonus]/12),_DimMonth)
Proud to be a PBI Community Champion
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |