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, …) and few dimension tables like Date, Channel, Product and also buyer with segmentation (importance) and sales rep unique link

I succeed to create measures for Coverage, Month over month change, week over week, but I am facing issue where I would like to present in same pivot:
- plan vs realization (activities)


What would be best way to do? I don't have issues with calucations on historical data, but with data on plan and to show the realisation... If anyone can help with some idea... Please let me know if I should make something more clear...

 

this works well - Coverage:=DISTINCTCOUNT(Activities[Unique link])/COUNTA(Plan[Unique link]) - i can create pivot by sales rep in rows, 

but if i create another measure like Plan:=COUNTA(Plan[Unique link]) i get full counting and not per sales rep like i get coverage... clearly i need measure respecting row filter...?

 

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.

     

4 Replies

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

         

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    al1981 , You have already created one measure for coverage now you can create two more measure for Plan and Realization using calculate and ALLexcept function

     

    Plan =
    CALCULATE(
    COUNTA(Plan[Unique link]),
    ALLEXCEPT(Plan, Plan[Sales Rep])
    )

     

     

     

    Realization =
    CALCULATE(
    COUNTA(Activities[Unique link]),
    ALLEXCEPT(Activities, Activities[Sales Rep])
    )