Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Margin % calculation with same name and multiple rows

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...
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Anonymous 

 

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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous 

     

    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.