Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a Depts Lookup table and a master data in my data model as shown below. I have created a measure using SWITCH statement to get the status depending on the delta between plan vs actuals.
The measure I used is as below :
Status = Switch(
True(),
Master_Data[Delta%]> 0 && Master_Data[Detla 2 Wks%]>0, "Already Overbudget, yet adding to +ve Delta",
Master_Data[Delta%]< 0 && Master_Data[Detla 2 Wks%] <0, "Already Underbudget, yet adding to -ve Delta",
Master_Data[Delta%]> 0 && Master_Data[Detla 2 Wks%]< 0, "Improvements to +ve Delta",
Master_Data[Delta%]<0 && Master_Data[Detla 2 Wks%]>0, "Improvements to -ve Delta"
)
Here Delta% is the measure for calculating % difference between plan vs actuals for the entire period, Delta 2 Wks% is the measure for calculating % difference between plan vs actuals for the last 2 weeks.
I am able to get the status output as per below table 1 in Power BI, but I need to present the results as per table 2 provided below could you please help..
Output I am able to get (table 1) :
Function Mapping | Status |
Design | Already Overbudget, yet adding to +ve Delta |
DV | Already Overbudget, yet adding to +ve Delta |
PD | Already Overbudget, yet adding to +ve Delta |
SET | Already Overbudget, yet adding to +ve Delta |
SPT | Improvements to +ve Delta |
DFT | Improvements to -ve Delta |
Output Required (table 2) :
Status Function
Already Overbudget, yet adding to +ve Delta | Design,DV,PD,SET |
Already Underbudget, yet adding to -ve Delta | - |
Improvements to +ve Delta | SPT |
Improvements to -ve Delta | DFT |
Thank You,
Rajesh Divate
Solved! Go to Solution.
Hi @rajdivate ,
Please create a table first.
Table =
SUMMARIZE(
'Depts_LookUP',
'Depts_LookUP'[Function Mapping],
"Status",
[Status]
)
Then create a calculated column on this table.
Functions =
CONCATENATEX(
FILTER('Table','Table'[Status]=EARLIER('Table'[Status])),
'Table'[Function Mapping],","
)
Enter data.
Table2.
Create relationships.
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
Thank You Very Much!
Hi @rajdivate ,
Please create a table first.
Table =
SUMMARIZE(
'Depts_LookUP',
'Depts_LookUP'[Function Mapping],
"Status",
[Status]
)
Then create a calculated column on this table.
Functions =
CONCATENATEX(
FILTER('Table','Table'[Status]=EARLIER('Table'[Status])),
'Table'[Function Mapping],","
)
Enter data.
Table2.
Create relationships.
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |