cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
twb311 Frequent Visitor
Frequent Visitor

Target values do not match between tables

Hi,

 

I have two tables in the linked example below.  The first table accurately displays the target value that I want and expect for each product.

 

The second table displays the avg quantity sold for each product.  I would like to display the target value next to the avg quantity sold.  But I don't get the expected value for target on the second table.  The expected value is the target on this first table.

 

Can you help?

 

https://drive.google.com/open?id=11vzlQW0WF1g2jUvmLJNlPgNm3AGdcs7U

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

Re: Target values do not match between tables

Thanks for the sample file, makes everything a lot easier

Is this what you were looking for ?target.PNG
when you see a column returning all the same value it usually indicates some problem with the filter context

I wrote a measure to store the value of the current product, then use that value to lookup the target direct from the target table

Target Value =
VAR current_product = selectedvalue('Product Mix'[Product])
var result= lookupvalue(Target[Target Value],Target[Product],current_product)
return result
The filter context is a very powerful tool in DAX, but often you need to escape it... this is one of the reasons for starting to learn how to write measures. Hope this helps.

I'm a personal Power Bi Trainer I learn something every time I answer a question

The Golden Rules for Power BI

  1. Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
  2. Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
  3. Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
  4. Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result  to check on your steps along the way.
 




Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




View solution in original post

3 REPLIES 3
Super User II
Super User II

Re: Target values do not match between tables

Thanks for the sample file, makes everything a lot easier

Is this what you were looking for ?target.PNG
when you see a column returning all the same value it usually indicates some problem with the filter context

I wrote a measure to store the value of the current product, then use that value to lookup the target direct from the target table

Target Value =
VAR current_product = selectedvalue('Product Mix'[Product])
var result= lookupvalue(Target[Target Value],Target[Product],current_product)
return result
The filter context is a very powerful tool in DAX, but often you need to escape it... this is one of the reasons for starting to learn how to write measures. Hope this helps.

I'm a personal Power Bi Trainer I learn something every time I answer a question

The Golden Rules for Power BI

  1. Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
  2. Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
  3. Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
  4. Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result  to check on your steps along the way.
 




Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




View solution in original post

twb311 Frequent Visitor
Frequent Visitor

Re: Target values do not match between tables

@kentylerthat's it!  thank you!

 

Boy do I still have a lot to learn when it comes to DAX and measures.  Got to start studying up on the VAR function.

 

thanks again.

Super User II
Super User II

Re: Target values do not match between tables

depending on how your head works... take a look at the free lessons and other material on sqlbi.com

or look at avi sing's learnpowerbi videos on youtube

or search on youtube for curbal

 





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors