Display Static Value in Matrix Column

[timstutzman]

Hello,

Is there a way to create a static value in a matrix column that is not affected by any slicers?

In my report, I need the Goal Plan number to be static.  For example above, I need the Goal Plan for this sales rep to be 1.35M no matter what the selections for the slicers are.  Each sales rep has a goal that he/she needs to sell by the end of the year.  This particular sales rep has a goal of 1.35M for this year.  Currently, this sales rep has sold 404K (CY) towards his goal of revenue brought in between the dates of 11/1/2021 to 1/8/2022.  We have a fiscal year of November to October.

First, I created a separate table to hold the goals and linked them to the sales rep, but that didn't work because as you expand down to the hierarchy of the matrix (Sales Rep > Channel > State > Customer Number >  Sales Invoice) the goal plan would display on every single invoice our system ever generated which led to rows under the sales rep that were not related to the sales rep. Once I thought about it, that made sense why that happened because it was only linked to the sales rep and not the revenue table where revenue associated to the sales rep is (i.e. CY revenue, PY revenue, etc.)

I then added the goal plan to the revenue table and that worked properly as I wanted, but only if I populated a revenue date for the goal plan.  If I left the revenue date blank it would get sliced out by the date slicer.  The problem is that the date slicer can be changed for different date ranges to see how much the sales rep has sold in different time periods, but if the date for the goal plan isn't within the date range it won't display.  So I go back to my question, is there a way to display a static value in a matrix that is not affected by slicers, and does not generate incorrect invoice rows under the sales rep in the hierarchy like when I put the goal plan in a separate table?

This has been stumping me for several days.  Any help would be appreciated.

Thanks,
Tim

[timstutzman]

Hello @goncalogeraldes 

Thank you for your response. You suggestion is in the right direction, but I am still having issues.

I need the output to look more like this:

Where each sales rep goal is associated to the sales rep and displays on the total line only.

When I do your suggesstion it adds up all the sales reps goals (10 different sales reps) and puts it on every line.  The good thing your suggestion is not effected by the date slicer like above but I need the goal to be associated to the reps hierarchy.

Thank you for your help and appreciate it if you have anymore suggestions.

Thanks,

Tim

[goncalogeraldes]

Hello there @timstutzman ! Assuming your goal field is a measure, have you tried using the ALL() function, for example:

Goal = SUMX(ALL(SalesRep_Goals), SalesRep_Goals[Amount]) 

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

[timstutzman]

Hi @goncalogeraldes 

I figured it out, I used the following and it worked.

CALCULATE( SUM( 'SalesData'[GoalPlan]), ALL(Dates[Date]))

Tim