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

[julesdude]

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! 

[julesdude]

@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.

[TheoC]

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

_2

After 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

[v-yalanwu-msft · Accepted solution]

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]

@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]

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.

[julesdude]

@TheoC @v-yalanwu-msft 

This may explain a bit better:

[TheoC]

@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]

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:

[TheoC]

@julesdude okay, thanks so much for sending that through. Unfortunately, due to your Table B only having Asset References and no country, you cannot achieve what you are wanting because there is no way for Power BI to determine which Asset Reference is associated with which country. 

For example, Both Italy and Egypt have the same Asset Reference (e.g. AAA can be either Italy or Egypt).  And, given you have linked Table A and Table B via a relationship at the Asset Reference level (which is NOT a unique key), Power BI does not know which Country to return because there is nothing distinct or unique to identify the Country that the Asset Reference belongs to.

I hope that makes sense?

Cheers mate.

Theo

[julesdude]

Hi @TheoC 

Thanks for looking at it. It sort of makes sense. I was a bit lost when you said AAA can be either Italy or Egypt because TableA is listing distinct references and the country column provides the country membership they belong to.
So if you're saying that the DAX can't lookup this reference to begin summing assets for each country, how else can I do this?
I had a thought - what if I merged the two tables/queries in Power Query so that I create a new column in Table B called Country which lists the corresponding country based on the asset reference join of Table A to Table B. We would then get the following:

This could be better to work with as now all held in the same table, but I'd still be uncertain how the DAX would operate and call requiring the logic above?

[TheoC]

Hi @julesdude 

As long as you have the ability to identify which Asset Reference belongs to which Country, then you can do it.  The issue in this instance, with the data that currently exists in Table A and Table B, it cannot be done.

When I was putting together a new measure for you which started with a LOOKUPVALUE as the first variable, it wasn't letting add 'Table A'[Country] as the third parameter being the Search Value.  The reason for this is because there's no way for Power BI to determine which Asset Reference belongs to a specific Country with respect to the population of data that currently exists.

Unless there is other data that you have which has not been shared, there is no way in which the outcome can be achieved unfortunately (from my understanding).

I hope this makes sense.

Theo

[julesdude]

Hi @TheoC 

Yes I'm afraid although I've simplified and I've taken a number of columns out that aren't relevant, this is all I've got that's relevant to this issue.
Ok, I have merged the query to bring a country reference column into table B so that I now have this:

Updated file:

File 

Can your DAX above be adapted to now just reference this table in some way?

[julesdude]

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 

[TheoC]

@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]

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

_2

2. 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

_2

I hope this helps 🙂

Theo

[julesdude]

@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.

[TheoC]

@julesdude apologies! I hit Accept Solution instead of Reply. I will provide you a solution shortly!