Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

October 28 & 29: Experts share their secrets on how to pass the Fabric Analytics Engineer certification exam—live. Learn more

Reply
P_BI_Noob
Frequent Visitor

% of revenue

Hello, looking for help with this formula that I wanted to calculate the percentage of revenue. Tried to find similar threads on the topic but none of the solutions seemed to help me. 

 

 

Here is my DAX expression - 

 

 

 

1 ACCEPTED SOLUTION

Hello, my table does indeed look exactly like what you have above. However the dax expression was still not working. I did figure out the problem.

I had sorted the Attribute column using another column called AttributeOrder which essentially looked like this 

var att = 'ProfitAndLossAsAllowed'[Attribute - Copy]
return
SWITCH(true(),
att = "Contract Revenues Earned",	            "01",
att = "Other Revenues",                            "02",
att = "Total Revenues",	                    "03",

 

So once I modified the DAX to this 

% of Revenue = 
VAR _PART1 =
    CALCULATE ( SUM ( ProfitAndLossAsAllowed[Value] ) )
VAR _PART2 =
    CALCULATE (
        SUM ( ProfitAndLossAsAllowed[Value] ),
        FILTER (
            ALL ( ProfitAndLossAsAllowed[Attribute], ProfitAndLossAsAllowed[AttributeOrder] ),
            ProfitAndLossAsAllowed[Attribute] = "Total Revenues"
        )
    )
VAR _DIVIDE =
    DIVIDE ( _PART1, _PART2, 0 )
RETURN
    _PART1 

I was getting the results as desired, thanks for your contribution.

View solution in original post

5 REPLIES 5
rubayatyasmin
Super User
Super User

@P_BI_Noob hi,

 

you're using the same SUM(ProfitAndLoassAsAllowed[Value]) for both numerator and denominator in the DIVIDE function, which might not be what you intended.

 

try this instead

% of Revenue = DIVIDE(
CALCULATE(
SUM(ProfitAndLoassAsAllowed[Value]),
ProfitAndLoassAsAllowed[Attribute] = "Specific Revenue Category"
),
CALCULATE(
SUM(ProfitAndLoassAsAllowed[Value]),
ProfitAndLoassAsAllowed[Attribute] = "Total Revenue"
),
0
)

 

In this formula, "Specific Revenue Category" should be replaced with the category of revenue you want to calculate the percentage for. 

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


Thank you for the response, however I would like the "Specific Revenue Category" to be all the attributes. If you look at the first 3 columns in my example, the desired result would be 

Contracts Revenue Earned

63,176100%
Total Revenues63,176100%
Cost of Contract Revenues Earned52,722.5183.45%
Total Costs52,72383.45%
Gross Profit10,45316.55%

 

Using "Contracts revenues earned" as the "Specific Revenue Category" in your DAX resulted in 

 

 

 

Hi @P_BI_Noob ,

 

I think your calculation is based on your data model. If your table looks like as below, your measure should work.

vrzhoumsft_0-1689838131771.png

vrzhoumsft_1-1689838451296.png

Your measure is based on two parts, one is sum of amount and another is the total revenue.

You can check _PART1 and PART2 by code as below. Check whether part1 or part2 could return correct result.

% of Revenue = 
VAR _PART1 =
    CALCULATE ( SUM ( ProfitAndLossAsAllowed[Value] ) )
VAR _PART2 =
    CALCULATE (
        SUM ( ProfitAndLossAsAllowed[Value] ),
        FILTER (
            ALL ( ProfitAndLossAsAllowed[Attribute] ),
            ProfitAndLossAsAllowed[Attribute] = "Total Revenues"
        )
    )
VAR _DIVIDE =
    DIVIDE ( _PART1, _PART2, 0 )
RETURN
    _PART1 

If this reply still couldn't help you solve your issue, please share a sample file with me and show me a screenshot with the result you want.

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hello, my table does indeed look exactly like what you have above. However the dax expression was still not working. I did figure out the problem.

I had sorted the Attribute column using another column called AttributeOrder which essentially looked like this 

var att = 'ProfitAndLossAsAllowed'[Attribute - Copy]
return
SWITCH(true(),
att = "Contract Revenues Earned",	            "01",
att = "Other Revenues",                            "02",
att = "Total Revenues",	                    "03",

 

So once I modified the DAX to this 

% of Revenue = 
VAR _PART1 =
    CALCULATE ( SUM ( ProfitAndLossAsAllowed[Value] ) )
VAR _PART2 =
    CALCULATE (
        SUM ( ProfitAndLossAsAllowed[Value] ),
        FILTER (
            ALL ( ProfitAndLossAsAllowed[Attribute], ProfitAndLossAsAllowed[AttributeOrder] ),
            ProfitAndLossAsAllowed[Attribute] = "Total Revenues"
        )
    )
VAR _DIVIDE =
    DIVIDE ( _PART1, _PART2, 0 )
RETURN
    _PART1 

I was getting the results as desired, thanks for your contribution.

@P_BI_Noob 

 

Here is the modified DAX

 

% of Revenue =
DIVIDE(
ProfitAndLoassAsAllowed[Value],
CALCULATE(
SUM(ProfitAndLoassAsAllowed[Value]),
ALL(ProfitAndLoassAsAllowed),
ProfitAndLoassAsAllowed[Attribute] = "Total Revenues"
),
0
)

 

Let me know the result. The problem is with the denominator and numerator. if it is still wrong, I would like to try myself witha demo data. If you want. So, a file will be appriciated. 


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

October NL Carousel

Fabric Community Update - October 2024

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