cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Frequent Visitor

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.

5 REPLIES 5
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.

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

Proud to be a Super User!

Frequent Visitor

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,176 100% Total Revenues 63,176 100% Cost of Contract Revenues Earned 52,722.51 83.45% Total Costs 52,723 83.45% Gross Profit 10,453 16.55%

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

Community Support

Hi @P_BI_Noob ,

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 ``````

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.

Frequent Visitor

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.

Super User

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.

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.