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.
I am trying to fix TOTALS of a Matrix but ending up with adding a BLANK row as shown in the image below
My measure is as below, I have values to replace 2, 3, 4, & 5 but keeping it simple here. Can you please help
Budget MTD =
If( HASONEVALUE(M_Hierarchy[L1]) ,
SWITCH( SELECTEDVALUE ( M_Hierarchy[L1] ),
"Rooms",
2,
3
)
,
SWITCH( SELECTEDVALUE ( M_Hierarchy[L1] ),
"Rooms",
4,
5
)
)
Solved! Go to Solution.
Issue Fix. There was problem in relations between tables
@Majidbhatti , Try a measure like
Budget MTD = sumx(values(M_Hierarchy[revenue]), // Use the correct table
If( HASONEVALUE(M_Hierarchy[L1]) ,
SWITCH( SELECTEDVALUE ( M_Hierarchy[L1] ),
"Rooms",
2,
3
)
,
SWITCH( SELECTEDVALUE ( M_Hierarchy[L1] ),
"Rooms",
4,
5
)
))
@mahoneypat , i wish it was that easy but BLANK is not showing in the visual filter
@amitchandak , All items are SUMXed which is blowing out the totals.
See the problem below
Budget MTD (Rev) =
Var vMonthDays = DATEDIFF( STARTOFMONTH(DateTable[Date]) , ENDOFMONTH(DateTable[Date])+1 , DAY )
Var vVal = CALCULATE(
TOTALMTD([Actual Amount (AX)], DateTable[Date],AX_Data[Type] = "B") ,
M_Hierarchy[L1Code] <> "9"
)
Return
If( HASONEVALUE(M_Hierarchy[L1]) ,
SWITCH( SELECTEDVALUE ( M_Hierarchy[L1] ),
"Rooms",
vVal,
(vVal / vMonthDays ) * SELECTEDVALUE(DateTable[DAY])
)
,
//THIS SECTION IS THE PROBLEM
CALCULATE(
TOTALMTD([Actual Amount (AX)], DateTable[Date],AX_Data[Type] = "B") ,
M_Hierarchy[L1Code] <> "9"
)
)
Issue Fix. There was problem in relations between tables
In the Filter panel, highlight that visual and uncheck the Blank box for the Revenue column. You could also do it with DAX with and AND or a nested IF, but that is easier.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |