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.
Hi,
I'm pretty lost on how to go about doing this. I have a dataset that contains totals tied to specific accounts which are tied to 2 business units (Unit 1 and Unit 2). However, each account has a Location Code tied to it. If the location code is 750, that indicates that the value needs to be split between the 2 units. In the sample below, the first totals of $44.64, $ 1652.63 and $ 6141.55 need to be split evenly between Unit 1 and 2.
I am unsure of how to accomplish this in Power BI. Is there some way I can do an IF statement to search for the '750' location code and then INSERT 2 new rows with the split USD_AMT?
Solved! Go to Solution.
Hi @arti_r
You can create two new columns called Business Unit 1 and Business Unit 2 and split the [USD_AMT] in between them. Although that implies that the columns that don't need to be splitted are going to somehow be splitted too ....
If that's OK for you ...
1.- Open the quety editor.
2.- Add a new personalized column called "Business Unit 1".
3.- Write the following code
if [LOCATION_CODE] = "750" then [USD_AMT] * 0.5 else [USD_AMT]
(you can change 0.5 for any other percentage if the split is not equal)
4.- Add a new personalized column called "Business Unit 2".
5.- Write the following code
if [LOCATION_CODE] = "750" then [USD_AMT] * 0.5 else null
6.- Apply the necessary data type transformation
7.- Close & Load
If you do this process you'll end up with a 'Business Unit 1' column with the splitted data for Business Unit 1 AND the not splitted data, and a 'Business Unit 2' column with only the Business 2 splitted data.
Hope that Helps
Vicente
Hi @arti_r
You can create two new columns called Business Unit 1 and Business Unit 2 and split the [USD_AMT] in between them. Although that implies that the columns that don't need to be splitted are going to somehow be splitted too ....
If that's OK for you ...
1.- Open the quety editor.
2.- Add a new personalized column called "Business Unit 1".
3.- Write the following code
if [LOCATION_CODE] = "750" then [USD_AMT] * 0.5 else [USD_AMT]
(you can change 0.5 for any other percentage if the split is not equal)
4.- Add a new personalized column called "Business Unit 2".
5.- Write the following code
if [LOCATION_CODE] = "750" then [USD_AMT] * 0.5 else null
6.- Apply the necessary data type transformation
7.- Close & Load
If you do this process you'll end up with a 'Business Unit 1' column with the splitted data for Business Unit 1 AND the not splitted data, and a 'Business Unit 2' column with only the Business 2 splitted data.
Hope that Helps
Vicente
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |