Forum Discussion

Pikachu-Power's avatar
Pikachu-Power
Icon for Impactful Individual rankImpactful Individual
5 years ago
Solved

Maximum Value in Bar Chart

hello all.

 

i have values which i present in a bar chart with year / quarter / month level. the measure for the values is:

 

Measure = CALCULATE(SUM(Table[Number]), FILTER(Calender, Calender[Month] = MAX(Calender[Month])))
 
For every month I see the SUM(Table[Number]), for quarter SUM(Table[Number]) of the last month, for year SUM(Table[Number]) of the last month like i want.
 
what not works is that i dont see the current year 2021 in the bar chart. And when i use the two arrows the MAX dont work any more. the MAX works only when i choose a exact year with drill modus.
 
Some ideas to optimize the formula? 
 
PS: my datasource goes until 2021 01
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Pikachu-Power , The max in the filter, use the context of row. This means if the row has a month(as a column) it will get that month as a max, If it has qtr and year, it will get a max month for that qtr and year.

     

    in case you need only one max try like

    Measure =
    var _max = MAXX(allselected(Calender), Calender[Month])
    return
    CALCULATE(SUM(Table[Number]), FILTER(Calender, Calender[Month] = _max))

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Pikachu-Power , The max in the filter, use the context of row. This means if the row has a month(as a column) it will get that month as a max, If it has qtr and year, it will get a max month for that qtr and year.

     

    in case you need only one max try like

    Measure =
    var _max = MAXX(allselected(Calender), Calender[Month])
    return
    CALCULATE(SUM(Table[Number]), FILTER(Calender, Calender[Month] = _max))

  • v-robertq-msft's avatar
    v-robertq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Pikachu-Power 

    According to your description, I can roughly figure out what’s your problem, you can try to expand your ‘Date’ table using the DAX:

    Date =

CALENDAR(DATE(2020,1,1),DATE(2021,12,31 ))

    If you still have a problem. I think you can post some sample data in table form and the chart with the error or a pbix file(without sensitive data) and your expected result(like the chart you want to get on your sample data)?

    Thanks very much!

    How to Get Your Question Answered Quickly 

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.