Row total to show value of max selected value

[tobirz]

Hi everyone,

Is there a way to have powerbi show the total to show the value of the max selected value.

For instance

Client Name	Location	January 2019	May 2021	Total
Client A	USA	20	10	10
Client B	Mexico	70	150	150
Client C	Japan	200	210	210

Please note that the month values is a calculated measure so I didnt get the option to show measure as: sum, average etc.

Regards

[v-rzhou-msft · Accepted solution]

Hi @tobirz ,

I think your table should look like as below.

Create a measure.

Measure = 
IF(
HASONEVALUE('Table'[Month]),
SUM('Table'[Value]),
VAR _MAXMONTH = MAX('Table'[Month]) 
RETURN 
CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Client Name]),'Table'[Month] = _MAXMONTH))
)

Result is as below.

Best Regards,
Rico Zhou

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

[amitchandak]

@tobirz ,

You can have measure

M1 =

maxx(Summarize(allselected(Table), Table[Client Name], Table[Location], "_1", [Measure]) , [_1])

use it with help from isinscope

if(not(isinscope(Table[Client Name]))  && not(isinscope(Table[Location])), [M1], [Measure])

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/