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.

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.

 

 

 

 

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

    julesdude definitely.  Can you send the data you're working off and I'll put a solution together for you first thing in the morning!

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

    julesdude  I have adapted v-yalanwu-msft solution. Can you try this as a measure. Just adjust column names to what they are in your data.

     

     

     

     

     

    Measure = 

VAR _1 = 

	CALCULATE ( 
		MAX ( 'Table'[Last Updated] ) ,
			ALLEXCEPT ( 'Table' , 'Table'[Country], 'Table'[Asset Reference] )
			)

RETURN

	CALCULATE ( 
		SUM ( 'Table'[Amount] ) ,
			FILTER ( 'Table'[Last Updated] = _1 )
			)

     

     

     

     

     

     

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

      Thanks so much TheoC 

      The only problem is that country needs to be called from the other table - Table A via the Asset Reference link. Will the above still work? I simplified the latest table so give an idea of what it should look like as one. But the country is looked up using the asset reference link:

       

       

       

       

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

        Hi TheoC v-yalanwu-msft 

         

        I've attached a sample workbook here removing unnecessary data and providing only needed columns as examples above. Data has been replaced slightly. :

         

        Example