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.
Hi all,
Want to ask for below chart. For chart A, I add a constant line for overall machine target (79.5%)
But when I choose certain machine (eg: 310 EA Machine) the target should be different, but as you can see below the constant line remain (79.5%) which is wrong.
Chart A
Chart B
I know constant line are no possible for my solution. Do you have suggestion how can I do a target line based on each machine (filter).
Regards,
Nuha
Solved! Go to Solution.
Hi @nuhasan ,
I created some data:
Here are the steps you can follow:
1. Create calculated table.
Slicer_Machine =
DISTINCT('Table'[Machine])
Slicer_Yearmonth =
DISTINCT('Table'[Year & Month])
2. Create measure.
select_value =
var _year=SELECTEDVALUE('Slicer_Yearmonth'[Year & Month])
var _group=SELECTEDVALUE('Slicer_Machine'[Machine])
var _month=CALCULATE(MAX('Table'[Date].[Month]),FILTER(ALL('Table'),'Table'[Year & Month]=_year))
return
DIVIDE(
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Date].[Month]=_month&&'Table'[Machine]=_group)),
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Date].[Month]=_month)))
month_amount =
var _year=SELECTEDVALUE('Slicer_Yearmonth'[Year & Month])
var _group=SELECTEDVALUE('Slicer_Machine'[Machine])
var _month=CALCULATE(MAX('Table'[Date].[Month]),FILTER(ALL('Table'),'Table'[Year & Month]=_year))
return
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Date].[Month]=_month)
)
Flag =
var _year=SELECTEDVALUE('Slicer_Yearmonth'[Year & Month])
var _group=SELECTEDVALUE('Slicer_Machine'[Machine])
var _month=CALCULATE(MAX('Table'[Date].[Month]),FILTER(ALL('Table'),'Table'[Year & Month]=_year))
return
IF(
FORMAT(MAX('Table'[Date]),"mmmm")=_month,1,0)
3. Place [Flag]in Filters, set is=1, apply filter.
4. Result:
Machine == A
Machine == B
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 @nuhasan ,
I created some data:
Here are the steps you can follow:
1. Create calculated table.
Slicer_Machine =
DISTINCT('Table'[Machine])
Slicer_Yearmonth =
DISTINCT('Table'[Year & Month])
2. Create measure.
select_value =
var _year=SELECTEDVALUE('Slicer_Yearmonth'[Year & Month])
var _group=SELECTEDVALUE('Slicer_Machine'[Machine])
var _month=CALCULATE(MAX('Table'[Date].[Month]),FILTER(ALL('Table'),'Table'[Year & Month]=_year))
return
DIVIDE(
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Date].[Month]=_month&&'Table'[Machine]=_group)),
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Date].[Month]=_month)))
month_amount =
var _year=SELECTEDVALUE('Slicer_Yearmonth'[Year & Month])
var _group=SELECTEDVALUE('Slicer_Machine'[Machine])
var _month=CALCULATE(MAX('Table'[Date].[Month]),FILTER(ALL('Table'),'Table'[Year & Month]=_year))
return
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Date].[Month]=_month)
)
Flag =
var _year=SELECTEDVALUE('Slicer_Yearmonth'[Year & Month])
var _group=SELECTEDVALUE('Slicer_Machine'[Machine])
var _month=CALCULATE(MAX('Table'[Date].[Month]),FILTER(ALL('Table'),'Table'[Year & Month]=_year))
return
IF(
FORMAT(MAX('Table'[Date]),"mmmm")=_month,1,0)
3. Place [Flag]in Filters, set is=1, apply filter.
4. Result:
Machine == A
Machine == B
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,
How should the target be calculated? What is the logic of that calculation?
Hi @Ashish_Mathur ,
There is no calculaton for target. We just input the target based on virtual discussion.
For example,
Machine | Target | Result for May22 (202205) |
310 EA Machine | 94.6% | 80.99% |
Regards,
Nuha
@nuhasan , Then you need to create a measure and use that as a constant line or create a clustered bar visual
Switch (true() ,
not(isfiltered(Table[Machine])) , 79.5 ,
max(Table[Machine]) = "310 EA Machine", 70.0
)
Add others as per need
For clustered bar visual, how to combine stacked column chart with line chart?
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |