Margin % calculation with same name and multiple rows

[dinesharivalaga]

Hi Experts,

I am working on revenue margin calculation in my dashboard.

But it is not giving the result as expected and it is summing the % (YTD CM% --> 45+53 = 98% for CM%) --> Data source from SP list

When multiple rows with same account names listed --> in that case revenue will summing up and CM% calculation mentioned below.

Actual revenue = Total YTD revenue * YTD CM%

CM % = Total Actual Revenue / Total YTD Revenue 
As per the data , 276909.78 / 565122 = 48%

So 48% will be the output on both CM% rows

DAX i have tried :

CM % = CALCULATE(DIVIDE([Total Actual Revenue],[Total YTD Revenue],0),ALLEXCEPT('Test Delivery Updates','Test Delivery Updates'[Account Name]))

please help me to achieve this.

Thanks

DK

[Anonymous]

Hi @dinesharivalaga 

Please correct me if I've misunderstood. [Total YTD Revenue] seems to be the measure you created, try the calculated column instead.

If it doesn't work, please provide some sample data and describe all the meaures or columns you used.

I tested this with simple data and when I used the measure, the values for the same account name would not aggregate, changing to a calculated column gives the desired result..

Total YTD Revenue(measure) = CALCULATE(SUM('Test Delivery Updates'[YTD Revenue]), ALLEXCEPT('Test Delivery Updates','Test Delivery Updates'[Account Name]))

CM % = CALCULATE(DIVIDE([Total Actual Revenue],[Total YTD Revenue(measure)],0),ALLEXCEPT('Test Delivery Updates','Test Delivery Updates'[Account Name]))

Total YTD Revenue(calculate column) = CALCULATE(SUM('Test Delivery Updates'[YTD Revenue]), ALLEXCEPT('Test Delivery Updates', 'Test Delivery Updates'[Account Name]))

_CM% = 
CALCULATE(
    DIVIDE(
        [Total Actual Revenue],
        CALCULATE(SUM('Test Delivery Updates'[Total YTD Revenue(calculate column)]), ALLEXCEPT('Test Delivery Updates', 'Test Delivery Updates'[Account Name])),
        0
    ),
    ALLEXCEPT('Test Delivery Updates', 'Test Delivery Updates'[Account Name])
)

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

[dinesharivalaga]

@Anonymous  Thanks for your solution .. here the challenge is we have done an unpivot column method in few selected columns , so after that all the accounts were duplicated (27 rows each) . so i have used DISTINCT in the "Total YTD Revenue" measure to filter the single unique revenue numbers .

In this case we have same values for both the accounts (same account name also) , so that it is not summing up and giving unique values. only different thing in this each row is "Offer Type" column.

Existing DAX :

Total YTD Revenue = SUMX(VALUES('Test Delivery Updates'[YTD Revenue]),CALCULATE(DISTINCT('Test Delivery Updates'[YTD Revenue])))

Here cannot able to use ALLEXCEPT like your code..

Please advise..

[Anonymous]

Hi @dinesharivalaga 

Could you please provide some sample data that maintains the same data structure? And describe which columns you are selecting for the unpivot operation.

Thank you in advance for your cooperation.

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

[Selva-Salimi]

Hi @dinesharivalaga 

I think you should update your DAX as follows:

CM % = DIVIDE(calculate([Total Actual Revenue],ALLEXCEPT('Test Delivery Updates','Test Delivery Updates'[Account Name])) , calculate([Total YTD Revenue] ,ALLEXCEPT('Test Delivery Updates','Test Delivery Updates'[Account Name])) ,0)

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.

[dinesharivalaga]

@Selva-Salimi  Thanks for your response 🙂

I didn't see any changes on the results , it is still same 98% 😞
Total YTD Revenue values are same on both rows so that it is not summing up .
In this case denominator is not summed up each other , it is just showing unique values.