Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Everyone,
I am bit struggling with the grand totals. Somehow the grand totals are not matching for few rows horizontally but works for others.
Below is the screenshot of the data and the dax code.
The vertical date is a posting date calendar and horizontal is the normal calendar.
The pattern where it does not work is whenever a row contains a Zero/Null value.
Dax code:
VAR prevmonth =
CALCULATE (
[M1] ,
PREVIOUSMONTH ( 'Calendar P'[P Date] )
)
RETURN
CALCULATE (
IF (
ISCROSSFILTERED ( 'Calendar P'[P D] ),
IF (
Max('C P'[P MonthKey]) <= (Max('C'[MonthKey])+100),
[M1] - prevmonth,
BLANK ()
),
CALCULATE (
[M1],
'T1'[Version] = 1
)
)
)
Any help is really appreciated.
Thanks in advance.
@medaxing , force total from rows
VAR prevmonth =
CALCULATE (
[M1] ,
PREVIOUSMONTH ( 'Calendar P'[P Date] )
)
RETURN
CALCULATE (sumx(values(Table[column]), //Column you have used in column(pivot) of matrix
IF (
ISCROSSFILTERED ( 'Calendar P'[P D] ),
IF (
Max('C P'[P MonthKey]) <= (Max('C'[MonthKey])+100),
[M1] - prevmonth,
BLANK ()
),
CALCULATE (
[M1],
'T1'[Version] = 1
)
)))
Refer why grand total is wrong -https://www.youtube.com/watch?v=ufHOOLdi_jk
Thanks Amit for the quick response.
No luck though.
I tried to separate the measure as well, still the same issue.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |