Forum Discussion
Dynamic Gauge Target based on date slicer
Two Tables:
| Sales | ||||
| Date | Salesperson | Region | Type | Amount |
| 4/4/2021 | Tim | west | red | 10 |
| 4/7/2021 | Jane | west | green | 12 |
| 4/15/2021 | Kate | east | red | 23 |
| 4/16/2021 | John | east | blue | 15 |
| 4/19/2021 | Tim | north | blue | 30 |
| 4/19/2021 | Kate | south | green | 12 |
| 4/23/2021 | Kate | south | green | 22 |
| 4/25/2021 | Jane | north | red | 32 |
| 4/30/2021 | Tim | west | blue | 16 |
| 5/1/2021 | John | south | green | 20 |
| 5/1/2021 | Jane | north | red | 10 |
| 5/3/2021 | John | south | red | 12 |
| 5/5/2021 | Kate | east | green | 19 |
| 5/10/2021 | Jane | west | blue | 18 |
| 5/10/2021 | Tim | west | blue | 23 |
| 5/11/2021 | Tim | west | red | 25 |
| 5/15/2021 | John | south | red | 33 |
| 5/18/2021 | Kate | south | green | 30 |
| Targets | ||
| Month | Type | Target |
| April | red | 35 |
| April | green | 30 |
| April | blue | 40 |
| May | red | 30 |
| May | green | 35 |
| May | blue | 45 |
I want a gauge that shows total monthly sales for a type with target for that type. The gauge would be bound to date slicer that would update sum of monthly sales and select corresponding target for that month. The slicer would also interact with other visuals on my dashboard.
4 Replies
Hi Microbz ,
Create MonthType columns in both tables.
MonthType = FORMAT([Date],"MMMM")&"-"&[Type]MonthType = [Month]&"-"&[Type]Create two measure. Put one of them to Filters, and set Show items when the value is 1.
Whether to complete the goal = IF(CALCULATE(SUM('Sales'[Amount]),FILTER(ALL(Sales),[MonthType]=MAX('Sales'[MonthType])))>=SUM('Targets'[Target]),"completed","not completed")Measure = IF(MAX('Targets'[Month]) in ALLSELECTED('Targets'[Month]),1)Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks Anonymous for the response. However, what I was looking for a way to use the monthly targets (table 2) as the target value on a PBI gauge. That way the gauge target would update based on the selected month. So, I would have a gauge for each "type" that would show the sum of sale amounts for that "type" with the target for that "type" for the selected month.
Hi Microbz ,
Do you mean that your Target table is actually your expected result? If not, please give an example to give the expected result style, thank you.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This is what I'm shooting for. I would have multiple gauges each filtered on "TYPE" and all bound to date slicer. For each gauge I want the TYPE target related to the TARGET table with the TARGET and SALES tables bound to same date slicer. This way as the date slicer is changed (by month) the sales amounts and the target are updated on the gauges.
