Forum Discussion

Helper III

1 year ago

Solved

No summation showing for the variable in the return statement

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.

Need Help

kushanNa

1 year ago

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

7 Replies

kushanNa

Super User

1 year ago

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
Helper III
1 year ago
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

Super User

1 year ago

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

Super User

1 year ago

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

Super User

1 year ago

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
Helper III
1 year ago
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
  Super User
  1 year ago
  Yes, please send it to me the PBIX file

Forum Discussion

No summation showing for the variable in the return statement

7 Replies

Related content

issues with RETURN statement from variables

Variables in if statement

prevent data summation

OR Statement ?

SQL Statement opcion error