topic Re: Plan vs realization in DAX Commands and Tips

Plan vs realization

al1981 — Mon, 22 Jul 2024 21:35:00 GMT

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

Re: Plan vs realization

bhanu_gautam — Tue, 23 Jul 2024 04:42:48 GMT

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])
)

Re: Plan vs realization

Anonymous — Tue, 23 Jul 2024 05:52:11 GMT

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.

Re: Plan vs realization

al1981 — Tue, 23 Jul 2024 06:23:42 GMT

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 🙂

Re: Plan vs realization

Anonymous — Tue, 23 Jul 2024 09:33:30 GMT

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.