Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello I need help in figuring out the following ;
I am working on a matrix visual that contains 3 rows and 1 value.
the first thing that I would like to do is to change my subtotal/total to sum up only "type of facility" that are = to Credits , Guarantees , total Amount given.
However before adding those rows up, I would like change the "Total Amount Given". it has to be reduced by the projects that are assigned to it.
for example Bank code A the (total amount given) should be 500 - (60+50) - (66+22)= 302
so instead of showing 500 it should show 302 (which is basically the residual).
thus, the Bank Code A should equal to = 302$(residual) + 110$(P111) + 88$(P222)+70$(P333)= 570 $ (instead of $906)
Solved! Go to Solution.
Hi @aseel_abs ,
According to your description, here's my solution. Create a calculated column.
Column =
VAR _SUM =
SUMX (
FILTER (
'Table',
'Table'[BANK CODE] = EARLIER ( 'Table'[BANK CODE] )
&& 'Table'[TYPE OF FACILITY]
IN { "CREDITS", "GUARANTEES" }
&& CONTAINSSTRING (
MAXX (
FILTER (
'Table',
'Table'[BANK CODE] = EARLIER ( 'Table'[BANK CODE] )
&& 'Table'[PROJECT CODE] = "-"
),
'Table'[TYPE OF FACILITY]
),
'Table'[PROJECT CODE]
)
),
'Table'[LIMIT AMOUNT]
)
RETURN
IF (
CONTAINSSTRING ( 'Table'[TYPE OF FACILITY], "TOTAL AMOUNT GIVEN" ),
'Table'[LIMIT AMOUNT] - _SUM,
IF (
'Table'[TYPE OF FACILITY] IN { "CREDITS", "GUARANTEES" },
'Table'[LIMIT AMOUNT]
)
)
Get the result.
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @aseel_abs ,
According to your description, here's my solution. Create a calculated column.
Column =
VAR _SUM =
SUMX (
FILTER (
'Table',
'Table'[BANK CODE] = EARLIER ( 'Table'[BANK CODE] )
&& 'Table'[TYPE OF FACILITY]
IN { "CREDITS", "GUARANTEES" }
&& CONTAINSSTRING (
MAXX (
FILTER (
'Table',
'Table'[BANK CODE] = EARLIER ( 'Table'[BANK CODE] )
&& 'Table'[PROJECT CODE] = "-"
),
'Table'[TYPE OF FACILITY]
),
'Table'[PROJECT CODE]
)
),
'Table'[LIMIT AMOUNT]
)
RETURN
IF (
CONTAINSSTRING ( 'Table'[TYPE OF FACILITY], "TOTAL AMOUNT GIVEN" ),
'Table'[LIMIT AMOUNT] - _SUM,
IF (
'Table'[TYPE OF FACILITY] IN { "CREDITS", "GUARANTEES" },
'Table'[LIMIT AMOUNT]
)
)
Get the result.
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.