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

Dynamic target

Hello, 

 

Looking for great advice here...

I'd like to set up dynamic target to a KPI card, the target value list as below.  In Biz Unit "B", 3 items are further divided respectively. The column of item remain blank represents the "overall" target of the biz unit. 

 

I'd like to set up slicer for "year", "Business Unit" and "items" in the report:

  • If I select year "2020", Biz Unit "B", I hope the target would show "5030";
  • If I select "2020", Biz Unit "B", and item"Food", I hope the target would show "4651";

Please advise how I should write a DAX to fultill this?

Any suggestion or comment is appreciated, thanks in advance!!

 

YearBusiness UnitItemsTarget
2020A 21573
2020B 5030
2019A 23971
2019B 5589
2020BFood4651
2020BFlower5033
2020BDrink8663
2019BFood5592
2019BFlower5167
2019BDrink9625
1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Dynamic target

@OZMckenzie 

For year and the business unit it should work. so we need handle item

New measure like

if(isfiltered(Table[Items]), sum(table[Target]),calculate(sum(table[Target]),isblank(Table[Items])))

 

Refer : https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin


View solution in original post

4 REPLIES 4
Super User IV
Super User IV

Re: Dynamic target

@OZMckenzie 

For year and the business unit it should work. so we need handle item

New measure like

if(isfiltered(Table[Items]), sum(table[Target]),calculate(sum(table[Target]),isblank(Table[Items])))

 

Refer : https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin


View solution in original post

OZMckenzie
Frequent Visitor

Re: Dynamic target

This is very helpful, thank you very much! @amitchandak 

 

Microsoft
Microsoft

Re: Dynamic target

Hi @OZMckenzie ,

 

I suggest create a calculated table for slicer item and filter it without showing the blank value:

 

 

Table 2 = VALUES('Table'[Items])

 

 

Untitled picture2.png

 

Then I suggest you use the measure for goal:

 

 

Measure =

IF (

ISFILTERED ( 'Table 2'[Items] ),

CALCULATE (

MAX ( 'Table'[Target] ),

FILTER ( 'Table', 'Table'[Items] in DISTINCT('Table 2'[Items] ))

),

CALCULATE ( MAX ( 'Table'[Target] ), ISBLANK ( 'Table'[Items] )||'Table'[Items] = "")

)

 

 

 

Please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EVKIbloPrEhHoES8kY...

 

Best Regards,

Dedmon Dai

OZMckenzie
Frequent Visitor

Re: Dynamic target

Thank you @v-deddai1-msft  for your great advice.

I will try it as well.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors