cancel
Showing results for
Did you mean:

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.

New Member

## Measure that can give me the share of revenue by profitability segment

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?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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.

2 REPLIES 2
New Member

Thank you very much for the help, this seems like it solves my problem.

Anonymous
Not applicable

@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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors