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

     

     

     

     

     

     

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