DAX and PBI Beginner - Look up result with multiple conditions

[Aimeeclaird]

Hi, 

I have 2 tables - one is my main data table and the other is a table capturing targets. 

In my main table I have rows of data, with columns capturing the;

• Funder
• Contract
• Month and year (Jan 2020)

In my target table I have created similar;

• Funder
• Contract
• Month and year
• Target value £

The target value is the total expected value for that month, under that contract with the funder. 

I have written a measure already that looks at the main data and calculates the total submitted value per funder and contract.

I want to present a visual that shows:

For FUNDERA, under CONTRACTA, you submitted a total value of £X, your target was £Y so you're under/over target.

What would be the best way to structure my data? 

How do I use Dax to look at the target table and say, If its FunderA and ContractA and in Jan 2020 the target = £Y?

Please also tell me if i'm massively over complicating this!!

[Anonymous]

If you want to analyze figs over time, you have to have a Calendar. You also have to have dimensions: Funder and Contract. There'll be 2 fact tables that should be completely hidden from view. Only measures defined in them should be visible. Slicing must always be done through dimensions, never directly on facts.

In the Calendar (marked as Date table in the model) you should hide the granularities you're not interested in. For instance, you will not be interested in days, only in months and/or years, as much as I can say. Also, your granularity is month, so you can either apportion your figures evenly over each day or you can connect the first day of the month to the fact tables. Since days are going to be hidden, this will work correctly.

Then, once you have a proper dimensional design, you'll have 2 measures. The one you created (but adjusted to the design), and another one to show the target values. Something like:

[Target Value] = SUM( Targets[Target Value] )

 Now, you can slice and dice via dimensions and drop the measures on the canvass. If you want a KPI to show whether you're under/over target, you can create it based on those 2 measures. If you want a measure that'll tell you in words where you are you can use this:

[Under/Over Target] = IF( [Total Value] > [Target Value], "Over", "Under")

Hope this helps.

Best

D

[Aimeeclaird]

Thank you for taking the time to reply. I have included a date table in my model...I think!

As a beginner, I may be misunderstanding so apologies in advance. 

How do I handle the two tables if Table1 is the value of each sale, where Table2 is the target total value per month?

If I were to sum the total submitted value vs the sum of the total target value that wouldn't filter by the conditions required (contract, funder, month). 

I assume I need to figure out how to say:

1. What is the total actual submitted value for Jan 2020, Funder1 and contract1 = X

2. What is the target value of Jan 2020, funder1 and contract1 = Y

3. KPI (or a measure?) to show whether the actual is > or < the target?

[Anonymous]

---

@Aimeeclaird wrote:

Thank you for taking the time to reply. I have included a date table in my model...I think!

As a beginner, I may be misunderstanding so apologies in advance. 

How do I handle the two tables if Table1 is the value of each sale, where Table2 is the target total value per month?

>> You apportion the target value evenly across the days. You do this in Power Query. This way you'll be able to slice and dice by anything you want and you'll see both the total value and the target value. It does not matter that if you select different granularity than month, you'll get a target value. If you select the right granularity in your calendar, you'll get the right answer. And... there are ways to make sure that you don't see targets when the granularity is not the one you care about.

If I were to sum the total submitted value vs the sum of the total target value that wouldn't filter by the conditions required (contract, funder, month).

>> Of course it would. As I said, you'll connect dimensions to both fact tables, so slicing will be working on both.

I assume I need to figure out how to say:

1. What is the total actual submitted value for Jan 2020, Funder1 and contract1 = X

>> Easy. You just slice the dimensions by the values you want. The measure [Total Value] will give you the answer.

2. What is the target value of Jan 2020, funder1 and contract1 = Y

>> Easy. You just slice the dimensions by the values you want. The measure [Target Value] will give you the answer.

3. KPI (or a measure?) to show whether the actual is > or < the target?

>> Easy. I've shown you already how to do it. Refer to my previous post.

---

Best

D

[Anonymous]

One way is merge two queries and get your Target value in first table.

another is using lookupvalue function create new column in first table

NEw column=lookupvalue(table2[Target value],Table2[funder],Table1[funder],Table2[contract],Table1[contract],Table2[Month and year],Table1[Month and year])

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.