Measures Totals issue - Help please reworking the DAX

[StevenHarrison]

Hi All, I have a measures total issue that I need help with.

I have a table of Rent values, Gross Rent, Benefits Due and Net Rent columns.

In order to get the latest and most up to date figure I have to use a MAX measure on the 'created on Date time' if there are entries with the same date, otherwise it's ok (screenshot below to get the figure for 29/04/2024 19:51:01)

As per @Greg_Deckler post Measure Totals, The Final Word - Microsoft Fabric Community I understand this is an issue with totals in tables but am having difficulty in resolving the totals issues.

Example table below with incorrect totals:

Measures used:

Measures – Gross Rent

Gross Rent SumMAX = CALCULATE(

            [Gross Rent SUM],

'rk_balancehistory_Transform'[createdonDateTime] = MAX ('rk_balancehistory_Transform'[createdonDateTime])

            )

Measures – HB Benefits

Benefits Due SumMAX = CALCULATE(

                        [Benefits Due SUM],

'rk_balancehistory_Transform'[createdonDateTime] = MAX ('rk_balancehistory_Transform'[createdonDateTime])

            )

Measures – Net Rent

Net Rent SumMAX = CALCULATE(

                        [Net Rent SUM],

 'rk_balancehistory_Transform'[createdonDateTime] = MAX ('rk_balancehistory_Transform'[createdonDateTime])

            )

Please can you help me with reworking the measures to enable the totals to display properly.

Many thanks in advance.

[Greg_Deckler]

@StevenHarrison So the general solution to this is something like:

Measure Total Gross Rent = 
  VAR __Table = SUMMARIZE( 'Table', [TenancyRef], "__Value", [Gross Rent] )
  VAR __Result = IF( HASONEVALUE( 'Table'[TenancyRef] ), [Gross Rent], SUMX(__Table, [__Value] )
RETURN
  __Result

In rare cases, you could run into an issue between CALCULATE and SUMMARIZE. In that case, rework your DAX so that you don't need an explicit CALCULATE. For example:

Gross Rent = 
  VAR __Date = MAX( 'rk_balancehistory_Transform'[createdonDateTime] )
  VAR __Table = FILTER( 'Table', [Date] = __Date )
  VAR __Result = SUMX( __Table, [Gross Rent] )
RETURN
  __Result

Also, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

[StevenHarrison]

Hi @Greg_Deckler  thanks for the feedback, I tried the following and power bi stated there was a syntax error:

Gross Rent SumMAX =
VAR __Table = SUMMARIZE( 'rk_balancehistory_Transform', [rk_tenancyid], "__Value", [Gross Rent Adj])
VAR __Result = IF( HASONEVALUE( 'rk_balancehistory_Transform' [rk_tenancyid] ), [Gross Rent SUM], SUMX(__Table, [__Value] )
RETURN
__Result

Then Tried the following:

Gross Rent SumMAX =
VAR __Date = MAX( 'rk_balancehistory_Transform'[createdonDateTime] )
VAR __Table = FILTER( 'rk_balancehistory_Transform', [createdonDateTime] = __Date )
VAR __Result = SUMX( __Table, [Gross Rent Adj] )
RETURN
__Result

this returned the same as my original:
Gross Rent SumMAX = CALCULATE(
[Gross Rent SUM],
'rk_balancehistory_Transform'[createdonDateTime] = MAX ('rk_balancehistory_Transform'[createdonDateTime])
)

but this didn't solve the issue of the columns not totalling:

Please can you advise on any other methods?

[Greg_Deckler]

@StevenHarrison Should be:

Gross Rent SumMAX =
VAR __Table = SUMMARIZE( 'rk_balancehistory_Transform', [rk_tenancyid], "__Value", [Gross Rent SUM])
VAR __Result = IF( HASONEVALUE( 'rk_balancehistory_Transform' [rk_tenancyid] ), [Gross Rent SUM], SUMX(__Table, [__Value] ) )
RETURN
__Result

[StevenHarrison]

Hi @Greg  - one issue I have is that there may be more than one entry for a particular date, so I need the latest value to be summed and not any others for that date, as per screenshots below:

29/04/2024 should be £737.76 as it is the latest (MAX) value in the createdonDateTime column

22/04/2024 should be £1410.38 as it is the latest (MAX) value in the createdonDateTime column

I tried amending the DAX that you have supplied but I need to add the created on date filter that will work:

(incorrect DAX below with error message)

Gross Rent SumMAX =

VAR __Date = MAX( 'rk_balancehistory_Transform'[createdonDateTime] )

VAR __Table = FILTER(

SUMMARIZE( 'rk_balancehistory_Transform', [rk_tenancyid], "__Value", [Gross Rent SUM]

),

'rk_balancehistory_Transform', [createdonDateTime] = __Date

    )

VAR __Result = IF( HASONEVALUE( 'rk_balancehistory_Transform' [rk_tenancyid] ), [Gross Rent SUM], SUMX(__Table, [__Value] ) )

RETURN

__Result

[StevenHarrison · Accepted solution]

Hi @Greg_Deckler  - I created a IsNewestDatetime column with the following DAX:

IsNewestDatetime =

'rk_balancehistory_Transform'[createdonDateTime] =

CALCULATE(

    MAX('rk_balancehistory_Transform'[createdonDateTime]),

    ALLEXCEPT('rk_balancehistory_Transform', 'rk_balancehistory_Transform'[rk_tenancyid], 'rk_balancehistory_Transform'[CreatedOnDate])

)

this returns a true/false.

So the Gross rent Sum can be as simple as the following:

Gross Rent SUM = SUM(rk_balancehistory_Transform[Gross Rent Adj])

and set the IsNewestDateTime to True on the page filter, this then filtered the values correctly.