Forum Discussion

julesdude's avatar
julesdude
Icon for Post Partisan rankPost Partisan
4 years ago
Solved

Pie Chart Problems - Can't Add Values for Totals Using Last Date Only

Hi there,   Edit to my original post as it wasn't clear! I'm having difficulty getting my pie chart to work. I have a source table that's like this:   Last Updated Country Asset Reference T...
DAX
Need Help
Pie Chart
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, julesdude ;

    You could create a measure.

    total =
var _max=CALCULATE(MAX('TableA'[Date]),ALLEXCEPT(TableA,'TableA'[Country]))
return CALCULATE(SUM('TableB'[Total Value]),FILTER(TableB,[Date]=_max))

    The final show:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yalanwu-msft's avatar
v-yalanwu-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi, julesdude ;

You could create a measure.

total =
var _max=CALCULATE(MAX('TableA'[Date]),ALLEXCEPT(TableA,'TableA'[Country]))
return CALCULATE(SUM('TableB'[Total Value]),FILTER(TableB,[Date]=_max))

The final show:


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • TheoC's avatar
    TheoC
    Icon for Community Champion rankCommunity Champion
    4 years ago

    julesdude with the changes you require, v-yalanwu-msft has provided a good solution and I recommend that you accept this post as a solution if you can.

    • julesdude's avatar
      julesdude
      Icon for Post Partisan rankPost Partisan
      4 years ago

      Thanks v-yalanwu-msft and TheoC 

       

      I get this error when I implement the DAX, I think because of the references to two different tables:

      The syntax for Table A is incorrect.......etc.

       

      total =
var _max=CALCULATE(MAX('TableA'[Date]),ALLEXCEPT(TableA,'TableA'[Country]))
return CALCULATE(SUM('TableB'[Total Value]),FILTER(TableB,[Date]=_max))

      I had changed this to:

       

       

       

      total = 
var _max=CALCULATE(MAX('Table B'[Date],ALLEXCEPT('Table A'[Country]))
return CALCULATE(SUM('Table B'[Value]),FILTER('Table B'Date]=_max))

      ....to try use the date in Table B. The date in Table A isn't really relevant here because it's a last updated date more than anything else. It's the Date in Table B that I need to be the latest and have the MAX on to find the latest date for an asset reference.

       

      So basically I need listed the country field from Table A which provides the list of countries, then from Table B to be a sum of the latest date values by asset reference (like in table in my OP), to give you a table split by country giving you the total of the latest asset values.

      If you could help further then that would be great. I hope this makes sense.