Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am trying to get lastnonblank value in a measure .Its returning the correct value at row level.But at Grand total level its returning wrong value.It seems that it is taking July grand total insted of adding for each operation.
When I force to calculate grand total using sumx.It also return me wrong value.
After using above formula the grand total becomes 45,668,410
The correct Grand total value should be :-13,784,559
Thank you in advance.
Solved! Go to Solution.
Hi @kumarm ,
You can create this measure:
Result =
VAR lastnoblank =
CALCULATE (
[Total Income Forecast DP - Report],
LASTNONBLANK ( 'Dim Period'[PeriodDate], [Total Income Forecast DP - Report] )
)
VAR tab =
SUMMARIZE (
'Table',
'Table'[Operation],
"lastnoblank",
CALCULATE (
[Total Income Forecast DP - Report],
LASTNONBLANK (
'Dim Period'[PeriodDate],
CALCULATE ( [Total Income Forecast DP - Report] )
)
)
)
RETURN
IF (
HASONEVALUE ( 'Dim Period'[PeriodDate].[Month] ),
lastnoblank,
SUMX ( tab, [lastnoblank] )
)
Attached a sample file in the below, hopes to help you.
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
@kumarm This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
@kumarm , try a measure like
sumx( values(Table[operations]),[Total Income Forecast DP YE - Report test ])
or move the calculation in this measure
Thanks amitchandak for your Quick reply.
The solution that you suggested it works only when I create a separate measure using the existing one.
But If I want to see data based on another dimesion the value seems to be wrong.
I want to see the grand total correct when I select other dimension as well.
Can you please suggest how to handle this.
Hi @kumarm ,
You can create this measure:
Result =
VAR lastnoblank =
CALCULATE (
[Total Income Forecast DP - Report],
LASTNONBLANK ( 'Dim Period'[PeriodDate], [Total Income Forecast DP - Report] )
)
VAR tab =
SUMMARIZE (
'Table',
'Table'[Operation],
"lastnoblank",
CALCULATE (
[Total Income Forecast DP - Report],
LASTNONBLANK (
'Dim Period'[PeriodDate],
CALCULATE ( [Total Income Forecast DP - Report] )
)
)
)
RETURN
IF (
HASONEVALUE ( 'Dim Period'[PeriodDate].[Month] ),
lastnoblank,
SUMX ( tab, [lastnoblank] )
)
Attached a sample file in the below, hopes to help you.
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |