Forum Discussion
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 | Total Value |
| 02/03/2022 | Italy | aaa | 4,324 |
| 02/03/2022 | Italy | bbb | 42 |
| 04/01/2022 | Egypt | ccc | 56,540,000 |
| 16/03/2021 | Italy | aaa | 546 |
| 16/03/2021 | Italy | bbb | 16,730,000 |
| 18/12/2020 | Egypt | ccc | 546 |
| 24/08/2020 | Egypt | ccc | 4 |
| 24/08/2020 | Egypt | ccc | 6 |
| 27/08/2019 | Italy | aaa | 7,567 |
| 27/08/2019 | Italy | aaa | 56 |
| 27/08/2019 | Italy | aaa | 4,534 |
| 27/08/2019 | Italy | aaa | 53 |
| 27/08/2019 | Italy | bbb | 634 |
| 27/08/2019 | Italy | bbb | 42 |
| 27/08/2019 | Italy | bbb | 5,423 |
| 27/08/2019 | Italy | bbb | 534 |
| 23/07/2019 | Egypt | ccc | 635 |
| 22/07/2019 | Egypt | ccc | 6 |
| 21/07/2019 | Egypt | ccc | 457 |
| 20/07/2019 | Egypt | ddd | 4 |
| 19/07/2019 | Egypt | ddd | 657 |
| 18/07/2019 | Egypt | eee | 65,765 |
| 17/07/2019 | Egypt | eee | 746 |
| 16/07/2019 | Egypt | ddd | 74 |
| 15/07/2019 | Egypt | ddd | 5,435 |
I want a pie chart and a table to gather the total amount for each country using the latest date of each asset reference that is available and ignore values for any date prior to the latest date for that asset. So essentially, in table form (which i can also easily switch to a pie using the same logic) it would give me the following values:
| Country | Total Value |
| Italy | 4,366 |
| Egypt | 56,540,004 |
How can I achieve this? All I am getting at the moment is a total amount for Country that sums up every value against every date for every asset reference. All I want is country totals that only adds values of assets with that have the latest date only for that asset.
I hope that makes sense! Any help would be really appreciated!
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.
17 Replies
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 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.
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.
Hi julesdude
I used two calculated columns to achieve the below however I am confident this can be achieved more efficiently in some other way. Output below:
Two calculated columns are:
1. Max Date to return the latest date based on the country:
Max Date =
VAR _1 = 'Table (2)'[Country]
VAR _2 = MAXX ( FILTER ( ALL ('Table (2)' ) , 'Table (2)'[Country] = _1 ) , 'Table (2)'[Last Updated] )
RETURN
_22. Max Amount to return the maximum amount based on parameters specified:
Max Amount =
VAR _1 = CALCULATE ( MAX ('Table (2)'[Total Value] ) , ALLEXCEPT ( 'Table (2)' , 'Table (2)'[Country] ,'Table (2)'[Asset Reference] ,'Table (2)'[Last Updated] ) )
VAR _2 = IF ( AND ( _1 = 'Table (2)'[Total Value] , 'Table (2)'[Last Updated] = 'Table (2)'[Max Date] ) , _1 , 0 )
RETURN
_2I hope this helps 🙂
Theo
TheoC thank you I am very grateful for your help.
I think your solution might be difficult to implement for added DAX columns because in my example I did simplify things somewhat into one table. Technically I have two tables at work here (although they do have a relationship) which are joined by asset reference:
So if we are going to go the add column using DAX route, it might need to be modified?
Like you, I was thinking there could be a cleaner way of achieving this, but I'm happy to go with whatever works if the extra columns route is easier.
Any help appreciated.
Hi julesdude
Okay, I am hoping based on all the back and forths, this is the right outcome you are wanting:
Basically, the measure is saying to only return the Amount for the Country and Asset Reference if the date is the Last Date, and then make everything else disappear (just from the table visual).
Measure =
VAR _0 = LASTDATE ( 'Table'[Date] )
VAR _1 = CALCULATE ( LASTDATE( 'Table'[Date] ) , ALLEXCEPT ( 'Table' ,'Table'[Country] ,'Table'[Asset Reference] ) )
VAR _2 = IF ( _1 = _0 , SUM ('Table'[Amount] ) , BLANK() )
RETURN
_2After you shared the new data, it became a lot easier to follow. PBIX is attached.
Please mark this as the correct solution if it is what you're after!
Thanks heaps,
Theo
TheoC thank you so much for your time helping me on this.
And....it works perfectly for sub total values in the table.
The only issue now is that these are not reflected in the Grand Total, which is a problem because it is this total I'll need to be showing in the table I'm creating and the pie chart:
Can Grand Totals like this reference just the visible counterparts in the table? Is it even possible in Power BI?
Thanks again for all your help with this.
