Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!See when key Fabric features will launch and what’s already live, all in one place and always up to date. Explore the new Fabric roadmap
Hello,
I am looking for a solution for the following problem in my analysis. I have these customers/projects that each have a revenue and profitability assigned to them. I want to catogories these by the share of revenue for which profitability segments that they are in. I have made a measure(nested if statement) that can divide the customers/projects in their respective segments, and they are defined as:
Profitability Segment =
IF('Actuals'[Actual Profitability] >= 2.5 , 1 , IF('Actuals'[Actual Profitability] > 2.00, 2 , IF('Actuals'[Actual Profitability] >1.50, 3, 4)))
So if the proftability of the customer/project is greater than or equal to 2.5 then put it in the proftability segment 1, and if profitability of the customer/project is between 2.5 and 2.0, then put it profitability segment 2, and so on.
I have made this analysis before where my data was taking directly from a Excel file, in that case it was easy to solve since the profitability segment was a calculated column, that could be put in the legend, which gave me the desired result. However since my boss, now want to automate this analysis, I have been given a .pbix file, that have the data/model that I need, however the revenue and profitability is now measures. (that cant be used in a legend, as far as I am aware).
Share of Revenue for Profitability Segment 1 =
CALCULATE(Actuals[Actuals Revenue],
FILTER(
Actuals[Actual Profitability],
Actuals[Actual Profitability] >= 2.5
)
)
and
Share of Revenue for Profitability Segment 1 =
CALCULATE(
Actuals[Actuals Revenue],
FILTER(
Actuals[Actual Profitability Segment],
Actuals[Actual Profitability Segment] = 1
)
)
This doesnt work since the FILTER function doesnt take Actuals[Actual Profitability] or Actuals[Actual Profitability Segment] as a valid argument(since they are measure, I assume). My question is then, is it possible to even make a measure that can give me the share of revenue of each profitability segment, or do I to try another approach, like for example make a variable of some sort?
Solved! Go to Solution.
@AK1998-PowerBi , I believe you can achieve this with this type of measure structure:
Share of Revenue for Profitability Segment 1 for Projects =
SUMX(
VALUES('Project'[ProjectID]),
IF('Actuals'[Actual Profitability Segment] = 1, Actuals[Actuals Revenue])
)
This measure will loop through all distinct values of ProjectID that is in the filter context (i.e. after all the report filters and visual filters have been applied); for each ProjectID, if the Actual Profitability Segment is 1 then we add the Actuals Revenue of that ProjectID to the overall sum.
Hope this helps.
Thank you very much for the help, this seems like it solves my problem.
@AK1998-PowerBi , I believe you can achieve this with this type of measure structure:
Share of Revenue for Profitability Segment 1 for Projects =
SUMX(
VALUES('Project'[ProjectID]),
IF('Actuals'[Actual Profitability Segment] = 1, Actuals[Actuals Revenue])
)
This measure will loop through all distinct values of ProjectID that is in the filter context (i.e. after all the report filters and visual filters have been applied); for each ProjectID, if the Actual Profitability Segment is 1 then we add the Actuals Revenue of that ProjectID to the overall sum.
Hope this helps.
User | Count |
---|---|
19 | |
18 | |
15 | |
13 | |
13 |
User | Count |
---|---|
9 | |
8 | |
8 | |
6 | |
6 |