Forum Discussion

al1981's avatar
al1981
Helper II
2 years ago
Solved

Plan vs realization

I would like to do some analytics on activities of our sales rep... I think its pretty basic, but... I have one table with historical data (contacts with buyers and product details, dates, location,...
DAX
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi al1981 ï¼Œ

    If we add loops as a filter with salesperson as the filter, then we can make some changes to the original DAX.

    PlanCount = CALCULATE
(COUNTROWS('Plan'),
ALLEXCEPT(Plan,'Plan'[Cycle],'Plan'[Sales Rep]))

     

    Hope it helps!

    Best regards,
    Community Support Team_ Tom Shen

    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
2 years ago

Hi al1981 ï¼Œ

Thank you bhanu_gautam very much for the solution, and I've tried other ways to help you understand the problem:

According to your requirement, I think you are counting the plan will show all the counts, according to the data you gave which is 4,

but you want to count the sales reps individually according to their plan, here is my idea to realize it, maybe it can help you to solve the problem.

PlanCount = SUMX(VALUES('Plan'[Sales Rep]), CALCULATE(COUNTROWS('Plan'),FILTER(ALL('Plan'),Plan[Sales Rep]=MAX('Plan'[Sales Rep]))))
ActivityCount = SUMX(VALUES(Activities[Sales Rep]),CALCULATE(COUNTROWS('Activities'),FILTER(ALL('Activities'),'Activities'[Sales Rep]=MAX(Activities[Sales Rep]))))
PlanVsActivity = [ActivityCount]-[PlanCount]

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

 

  • al1981's avatar
    al1981
    Helper II
    2 years ago

    Dear Anonymous you were correct. Solution offered by bhanu_gautam  is something i already tried and i get all counted. Anyway, yours is closer but i would need that calculaton respects one filter more: Cycle. As in plan we have cycle 1 or 2 for same account (1st half a year or 2nd half) it should respect it as now it sums cycle and 2! But i am closer 🙂

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi al1981 ï¼Œ

      If we add loops as a filter with salesperson as the filter, then we can make some changes to the original DAX.

      PlanCount = CALCULATE
(COUNTROWS('Plan'),
ALLEXCEPT(Plan,'Plan'[Cycle],'Plan'[Sales Rep]))

       

      Hope it helps!

      Best regards,
      Community Support Team_ Tom Shen

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