% of revenue

[P_BI_Noob]

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 - 

[rubayatyasmin]

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

[P_BI_Noob]

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 

[v-rzhou-msft]

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.

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.

[P_BI_Noob · 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.

[rubayatyasmin]

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