Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Gross Rent SumMAX = CALCULATE(
[Gross Rent SUM],
'rk_balancehistory_Transform'[createdonDateTime] = MAX ('rk_balancehistory_Transform'[createdonDateTime])
)
Benefits Due SumMAX = CALCULATE(
[Benefits Due SUM],
'rk_balancehistory_Transform'[createdonDateTime] = MAX ('rk_balancehistory_Transform'[createdonDateTime])
)
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.
Solved! Go to Solution.
Hi @Greg_Deckler - I created a IsNewestDatetime column with the following DAX:
@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
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?
@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
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)
Hi @Greg_Deckler - I created a IsNewestDatetime column with the following DAX:
User | Count |
---|---|
93 | |
83 | |
77 | |
72 | |
65 |
User | Count |
---|---|
114 | |
101 | |
96 | |
65 | |
60 |