Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
AK1998-PowerBi
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.

View solution in original post

2 REPLIES 2
AK1998-PowerBi
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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors