No summation showing for the variable in the return statement

[samioberoi]

Hi,

I need some help on Power BI please. I have got three tables as FY_PREV , FILTER TABLE & S90(2). The relationship between  FY_PREV , FILTER TABLE in the actual working file is inactive because it otherwise gives the error message for its ambiguous path and there is no relationship with S90(2) table. Then in FY_PREV table i have created two measures as 

Total_Amount_outstanding =

VAR S17 = 33726

VAR S16 = 4135

VAR cat_Balance = CALCULATE(

    LOOKUPVALUE(

        'S90 (2)'[CAT BALANCE],

        'S90 (2)'[DOM], "MPA",

        'S90 (2)'[P_TYPE], "JDS LOANS P1"))

VAR Adjustment_payment = CALCULATE(

                        LOOKUPVALUE(

                            'S90 (2)'[Adjustment Pay],

                            'S90 (2)'[DOM], "MPA",

                            'S90 (2)'[P_TYPE], "JDS LOANS P1"))

RETURN

S17 + S16 + cat_Balance + Adjustment_payment


The other measure i have got is as below 

Measure_6 =

VAR L_Type =

            SWITCH(

                TRUE(),

                    SELECTEDVALUE(FY_PREV[PROD_NEW]) in {"SS","TL","SE"}

                     && SELECTEDVALUE( FY_PREV[DOM]) in {"ENGLAND","UNKNOWN"}

                     && SELECTEDVALUE(FY_PREV[P_CODE]) = "TMD",

                     "ML P1",

                    SELECTEDVALUE(FY_PREV[PROD_NEW]) = "FL"

                     && SELECTEDVALUE(FY_PREV[DOM]) = "ENGLAND"

                     && SELECTEDVALUE(FY_PREV[P_CODE]) = "TMD",

                     "FL P1",

                     SELECTEDVALUE(FY_PREV[PROD_NEW]) = "TT PRE REFORM"

                     && SELECTEDVALUE(FY_PREV[DOM]) in {"ENGLAND", "EUROPE","UNKNOWN"}

                     && SELECTEDVALUE(FY_PREV[P_CODE]) = "TMD",

                     "ML P2")

VAR sum_FY_PREV = CALCULATE(SUM(FY_PREV[BALANCE]),

                       USERELATIONSHIP(FY_PREV[Conditional_Column],'FILTER TABLE'[DOM]))

VAR Adjustedvalue = CALCULATE(

                          LOOKUPVALUE(

                            'S90 (2)'[WRITTEN OFF BAL],

                            'S90 (2)'[DOM], "MPA",

                            'S90 (2)'[P_TYPE], "JDS Loans P2"))

VAR Measure_Total_Amount = [Total_Amount_outstanding]

RETURN

IF(

    L_Type = "FL P1",

    sum_FY_PREV +IF(ISBLANK(Adjustedvalue),0,Adjustedvalue),

IF(L_Type= "ML P1",

sum_FY_PREV + Measure_Total_Amount,

sum_FY_PREV))

In the measure named as Measure_6 I want it to pick up a value from S90(2) table from WRITTEN OFF BALANCE column through Lookup where 'S90 (2)'[DOM] =  "MPA"       &     'S90 (2)'[P_TYPE] = "JDS Loans P2"   and then add that with the sum of value coming from sum_FY_PREV if L_Type is "FL P1". Similarly if the L_Type= "ML P1" then it should add that value summing up from FY_PREV[BALANCE] if it is ML P1 with the measure called Total_Amount_outstanding. 

 I am trying to send a link for uploaded PBI sample file on Github, but i am not sure if i uploaded it correctly and if it will work

Power-BI/ at main · benedictmooray/Power-BI
https://github.com/benedictmooray/Power-BI/tree/main

If this link doesn't work and there is any other way of me sharing the PBI sample file link, please let me know.

Appreciate your any help.

[kushanNa · Accepted solution]

Hi ,I think it's better If you divide this into few parts

first calculated sum of balance for FL P1

SumBalance_Flp1 = 
CALCULATE(
    SUM(fy_prev[balance]),
    fy_prev[conditional_column] = "FL P1"
)

then sum of balance for ML P1

SumBalance_mlp1 = 
CALCULATE(
    SUM(fy_prev[balance]),
    fy_prev[conditional_column] = "ML P1"
)

and the value coming from s90 table

S90 value = 
CALCULATE(
    SUM('S90 (2)'[WRITTEN OFF BAL]),
    'S90 (2)'[DOM] = "MPA",
    'S90 (2)'[P_TYPE] = "JDS Loans P2"
)

and then next you can create the the measure you want 

Measure_6new = 
VAR S17 = 33726
VAR S16 = 4135
RETURN
    [SumBalance_mlp1] + [S90 value] + S17 + S16

Total Amount outstanding = 
VAR S17 = 33726
VAR S16 = 4135
RETURN
[S90 value] + [SumBalance_Flp1]+ S17 + S16

[samioberoi]

Hi KushanNa,

Based on your calculate statement i used it in each variable separately and it did work. Thanks a lot for your help. 

[kushanNa]

if you need only two mesures you can try this out 

Measure_6new = 
VAR S17 = 33726
VAR S16 = 4135
VAR SumBalance_mlp1 = 
    CALCULATE(
        SUM(fy_prev[balance]),
        fy_prev[conditional_column] = "ML P1"
    )
VAR S90_value = 
    CALCULATE(
        SUM('S90 (2)'[WRITTEN OFF BAL]),
        'S90 (2)'[DOM] = "MPA",
        'S90 (2)'[P_TYPE] = "JDS Loans P2"
    )
RETURN
    SumBalance_mlp1 + S90_value + S17+S16

Total Amount outstanding = 
VAR S17 = 33726
VAR S16 = 4135
VAR SumBalance_Flp1 = 
    CALCULATE(
        SUM(fy_prev[balance]),
        fy_prev[conditional_column] = "FL P1"
    )
VAR S90_value = 
    CALCULATE(
        SUM('S90 (2)'[WRITTEN OFF BAL]),
        'S90 (2)'[DOM] = "MPA",
        'S90 (2)'[P_TYPE] = "JDS Loans P2"
    )
RETURN
    SumBalance_Flp1 + S90_value + S16 +S17

[kushanNa]

and if you want to make it work for any value in conditional_column you can use this 

S90 value = 
CALCULATE(
    SUM('S90 (2)'[WRITTEN OFF BAL]),
    'S90 (2)'[DOM] = "MPA",
    'S90 (2)'[P_TYPE] = "JDS Loans P2"
)

new measure = 
VAR S17 = 33726
VAR S16 = 4135
RETURN
    SUM(FY_PREV[BALANCE]) + [S90 value] + S17 + S16

[VahidDM]

Hi @samioberoi 

Try this, ensure your lookup values are numeric and replace blanks with 0. For example, modify your measure as follows:

Total_Amount_outstanding =
VAR S17 = 33726
VAR S16 = 4135
VAR cat_Balance =
    IF(
        ISBLANK(
            VALUE(
                LOOKUPVALUE(
                    'S90 (2)'[CAT BALANCE],
                    'S90 (2)'[DOM], "MPA",
                    'S90 (2)'[P_TYPE], "JDS LOANS P1"
                )
            )
        ),
        0,
        VALUE(
            LOOKUPVALUE(
                'S90 (2)'[CAT BALANCE],
                'S90 (2)'[DOM], "MPA",
                'S90 (2)'[P_TYPE], "JDS LOANS P1"
            )
        )
    )
VAR Adjustment_payment =
    IF(
        ISBLANK(
            VALUE(
                LOOKUPVALUE(
                    'S90 (2)'[Adjustment Pay],
                    'S90 (2)'[DOM], "MPA",
                    'S90 (2)'[P_TYPE], "JDS LOANS P1"
                )
            )
        ),
        0,
        VALUE(
            LOOKUPVALUE(
                'S90 (2)'[Adjustment Pay],
                'S90 (2)'[DOM], "MPA",
                'S90 (2)'[P_TYPE], "JDS LOANS P1"
            )
        )
    )
RETURN
    S17 + S16 + cat_Balance + Adjustment_payment

This ensures that LOOKUPVALUE returns numeric values (using VALUE) and that blanks are replaced with 0 so the summation works correctly.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

[samioberoi]

Hi VahidDM,

Thanks for trying to help. I tried your measure, but it doesn't work either. I don't know where the issue is. Were you able to access the sample data PBI file through the link?

Regards

[VahidDM]

Yes, please send it to me the PBIX file