Forum Discussion
Dynamic Slicer Quarter, Month
Hi,
I am building a dashboard which shows goals for each month. My goals table looks something like this
| Store Number | Month | Goal | Quarter |
| 1 | January | $10 | 1 |
| 2 | January | $20 | 1 |
| 3 | January | $30 | 1 |
| 1 | February | $20 | 1 |
| 2 | February | $30 | 1 |
| 3 | February | $40 | 1 |
| 1 | March | $30 | 1 |
| 2 | March | $40 | 1 |
| 3 | March | $50 | 1 |
| 1 | April | $40 | 2 |
| 2 | April | $50 | 2 |
| 3 | April | $60 | 2 |
| 1 | May | $50 | 2 |
| 2 | May | $60 | 2 |
| 3 | May | $70 | 2 |
| 1 | June | $60 | 2 |
| 2 | June | $70 | 2 |
| 3 | June | $80 | 2 |
| 1 | July | $70 | 3 |
| 2 | July | $80 | 3 |
| 3 | July | $90 | 3 |
| 1 | August | $100 | 3 |
| 2 | August | $90 | 3 |
| 3 | August | $100 | 3 |
| 1 | September | $110 | 3 |
| 2 | September | $100 | 3 |
| 3 | September | $110 | 3 |
| 1 | October | $120 | 4 |
| 2 | October | $110 | 4 |
| 3 | October | $120 | 4 |
| 1 | November | $130 | 4 |
| 2 | November | $120 | 4 |
| 3 | November | $130 | 4 |
| 1 | December | $140 | 4 |
| 2 | December | $150 | 4 |
| 3 | December | $160 | 4 |
I currntly have a slicer by Month name. The DAX to return the Goal for the selected month is
Goal =
CALCULATE (
SUM ('Goal table'[goal]),
FILTER (
ALLSELECTED ('Dates'), [Date]
IN FILTERS ('Dates'[Date]))
What I would like to do is be able to have a slicer which shows an Option to select the Quarter OR the Month Name and have the report update the goals based on the slicer. For example, if the report user selects Quarter 1 in the slicer, the report would look something like this:
| Store | Goal |
| 1 | $60 |
| 2 | $90 |
| 3 | $120 |
But if the report user selects the month of Juy for example, then the report would look something like this:
| Store | Goal |
| 1 | $70 |
| 2 | $80 |
| 3 | $90 |
I have done some research on Field Formatting, which sounds like what I would need. However, my understanding is that I need to have PBI on the cloud, which I do not and cannot get at this time.
Is there a work around to produce something above?
Thank you so much!!
Construct a proper mapping table for the slicer,
