Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all,
I have following situation:
Indicator for KPI is:
Solved! Go to Solution.
Hi @Pikachu-Power ,
According to your description, I create this data:
Here are the steps you can follow:
1. Create measure.
When the color is blue, the category is 1 and the maximum date:
Blue_1 =
var _1=MAXX(FILTER(ALL('Table'),'Table'[Categorie1]=1&&'Table'[Layer]="Blue"),'Table'[Date])
return
CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Layer]="Blue"&&'Table'[Date]=_1))
When the color is yellow, category is 1 and the maximum date:
Yellow_1 =
var _1=MAXX(FILTER(ALL('Table'),'Table'[Categorie1]=1&&'Table'[Layer]="Yellow"),'Table'[Date])
return
CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Layer]="Yellow"&&'Table'[Date]=_1))
2. Result.
You can downloaded PBIX file from here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Pikachu-Power ,
Here are the steps you can follow:
1. Create measure.
Blue_Max = MAXX(FILTER(ALLSELECTED('Table'),'Table'[Layer]="Blue"),'Table'[Value])
Yellow_Max = MAXX(FILTER(ALLSELECTED('Table'),'Table'[Layer]="Yellow"),'Table'[Value])
2. Place the Blue_Max and Yellow_Max for comparison on the Indicator and Target goals respectively, and display the date on the Trend axis
3. Result:
You can downloaded PBIX file from here.
This is the related link of KPI, hope to help you:
https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-kpi
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
But the SUM is missing:
Hi @Pikachu-Power ,
Not very clear about your description... If you want more filters like table [layer] = "blue", you can use the in function
Step description:
1. First use the summrize function to find a virtual table, which is grouped by Layer and some of the largest values of the date:
2. Use the IN function to select the desired Layer
Here are the steps you can follow:
1. Create measure.
Blue_Yellow =
var _summrize=
SUMMARIZE('Table','Table'[Layer],
"value",MAX('Table'[Value]),
"date",MAX('Table'[Date])
)
return
CALCULATE(SUM('Table 2'[Value]),FILTER('Table 2','Table 2'[Layer] in {"Blue","Yellow"}))
2. Result.
You can downloaded PBIX file from here.
If my answer is not what you need, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Liu,
Thanks for your reply. What i am looking for is somethink like:
Tab_Blue:
SUMMARIZE('Table','Table'[Categorie1],'Table'[Layer],
"value",MAX('Table'[Value]),
"date",MAX('Table'[Date])
)
with 'Table'[Categorie1] = 1 and 'Table'[Layer] = Blue (maybe it is possible to restrict the summarize table?) and
Tab_Yellow:
SUMMARIZE('Table','Table'[Categorie1],'Table'[Layer],
"value",MAX('Table'[Value]),
"date",MAX('Table'[Date])
)
with 'Table'[Categorie1] = 1 and 'Table'[Layer] = Yellow and final use these results in a KPI. Devide Tab_Blue / Tab_Yellow should be possible.
Hi @Pikachu-Power ,
According to your description, I create this data:
Here are the steps you can follow:
1. Create measure.
When the color is blue, the category is 1 and the maximum date:
Blue_1 =
var _1=MAXX(FILTER(ALL('Table'),'Table'[Categorie1]=1&&'Table'[Layer]="Blue"),'Table'[Date])
return
CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Layer]="Blue"&&'Table'[Date]=_1))
When the color is yellow, category is 1 and the maximum date:
Yellow_1 =
var _1=MAXX(FILTER(ALL('Table'),'Table'[Categorie1]=1&&'Table'[Layer]="Yellow"),'Table'[Date])
return
CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Layer]="Yellow"&&'Table'[Date]=_1))
2. Result.
You can downloaded PBIX file from here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
PS: The SUMMARIZE dont work. If you change for example 1600 with 2600 in Table1 for Blue 01.07.2020 then Table2 takes 2600 instead of 1800.
@Pikachu-Power , In this blue and yellow should both selected in slicer or none.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Hello all,
I have a sample pbix file in onedrive. But how to load it here? 🤔
hi all,
i couldnt load the pbix file without publihing private data so i will load the images:
you also have to create a calender table and connect with the table. For table i created following two measures:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |