Forum Discussion
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!!
| Year | Business Unit | Items | Target |
| 2020 | A | 21573 | |
| 2020 | B | 5030 | |
| 2019 | A | 23971 | |
| 2019 | B | 5589 | |
| 2020 | B | Food | 4651 |
| 2020 | B | Flower | 5033 |
| 2020 | B | Drink | 8663 |
| 2019 | B | Food | 5592 |
| 2019 | B | Flower | 5167 |
| 2019 | B | Drink | 9625 |
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/
4 Replies
Hi OZMckenzie ,
I suggest create a calculated table for slicer item and filter it without showing the blank value:
Table 2 = VALUES('Table'[Items])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/EVKIbloPrEhHoES8kY0d01EBqo5GLRmeE9kN0jewarnT2g?e=A82MD7
Best Regards,
Dedmon Dai
Thank you v-deddai1-msft for your great advice.
I will try it as well.
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/
This is very helpful, thank you very much! amitchandak
