Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Total sum is not coming.

I've created a measure, but it is not calculating total or may be I'm missing something in measure.  Test file.  https://filetransfer.io/data-package/jMu1r3na
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    If you change the measure [FFE_Saving_Curr_Yr_ETA_sum] to return VAR cal:

    ...
    )
VAR cal = container_count_last_yr - ( Lot_Vol_last_yr / fill_select )        // actual formula
VAR FFE_value =
    SELECTEDVALUE ( 'Test2'[FEE Value(from FFE Factor)])
VAR FFE = cal * FFE_value
RETURN
   cal

    We can use that in a SUMX measure that will return what I think you are looking for.

    _test = 
SUMX( 
    VALUES ( Test2[CsSizeType] ),
    [FFE_Saving_Curr_Yr_ETA_sum] *
    CALCULATE (
        SELECTEDVALUE ( 'Test2'[FEE Value(from FFE Factor)] )
    )
)

    I have uploaded the updated file here.  https://www.dropbox.com/s/kldgezrb6k8jpd0/dummy.pbix?dl=0

    The correction comes from the context transition of the CALCULATE in measure that I am still studying but I think the fix works.

jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
7 years ago

Hello Anonymous 

Your problem is coming from this line.

VAR FFE_value =
    SELECTEDVALUE ( 'Test2'[FEE Value(from FFE Factor)] )

When SELECTEDVALE receives more than one result it returns a blank unless you tell it what you want.  To test it you can change the RETURN of your measure to be.

RETURN
   FFE_value

And see the result, no FFE_value comes back on the total because it is getting more than one result.

My follow up post has a fix in it.  

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    7 years ago

    If you change the measure [FFE_Saving_Curr_Yr_ETA_sum] to return VAR cal:

    ...
    )
VAR cal = container_count_last_yr - ( Lot_Vol_last_yr / fill_select )        // actual formula
VAR FFE_value =
    SELECTEDVALUE ( 'Test2'[FEE Value(from FFE Factor)])
VAR FFE = cal * FFE_value
RETURN
   cal

    We can use that in a SUMX measure that will return what I think you are looking for.

    _test = 
SUMX( 
    VALUES ( Test2[CsSizeType] ),
    [FFE_Saving_Curr_Yr_ETA_sum] *
    CALCULATE (
        SELECTEDVALUE ( 'Test2'[FEE Value(from FFE Factor)] )
    )
)

    I have uploaded the updated file here.  https://www.dropbox.com/s/kldgezrb6k8jpd0/dummy.pbix?dl=0

    The correction comes from the context transition of the CALCULATE in measure that I am still studying but I think the fix works.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks for quick response..