Forum Discussion

samioberoi's avatar
samioberoi
Icon for Helper III rankHelper 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...
Need Help
  • kushanNa's avatar
    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

     

     

     

     

     

     

VahidDM's avatar
VahidDM
Icon for Super User rankSuper 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's avatar
samioberoi
Icon for Helper III rankHelper 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's avatar
    VahidDM
    Icon for Super User rankSuper User
    1 year ago

    Yes, please send it to me the PBIX file