Forum Discussion

ViralPatel212's avatar
ViralPatel212
Resolver I
2 years ago
Solved

Sum total based on filter parameters

Hello Team,   I was hoping if you could help me.   I have a scenario where i am calculating the Total Volume based on the following scenario. 1) IF one currency is selected than show currency To...
Need Help
Show and Tell
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    2 years ago

    ViralPatel212 , Try if this measure can help

     

    Total Volume =
    VAR _currencyCount = [currency count]
    VAR _buy = CALCULATE([MV Size Actual EUR], 'glimpse-prod-db output_file_dtl'[side] = "Buy")
    VAR _sell = CALCULATE([MV Size Actual EUR], 'glimpse-prod-db output_file_dtl'[side] = "Sell")
    VAR _total = _buy + _sell

    RETURN
    IF (
    _currencyCount >= 43,
    _total,
    IF (
    HASONEVALUE('glimpse-prod-db output_file_dtl'[side]),
    IF (
    SELECTEDVALUE('glimpse-prod-db output_file_dtl'[side]) = "Buy",
    _buy,
    _sell
    ),
    BLANK()
    )
    )

amitchandak's avatar
amitchandak
Super User
2 years ago

ViralPatel212 , Try if this measure can help

 

Total Volume =
VAR _currencyCount = [currency count]
VAR _buy = CALCULATE([MV Size Actual EUR], 'glimpse-prod-db output_file_dtl'[side] = "Buy")
VAR _sell = CALCULATE([MV Size Actual EUR], 'glimpse-prod-db output_file_dtl'[side] = "Sell")
VAR _total = _buy + _sell

RETURN
IF (
_currencyCount >= 43,
_total,
IF (
HASONEVALUE('glimpse-prod-db output_file_dtl'[side]),
IF (
SELECTEDVALUE('glimpse-prod-db output_file_dtl'[side]) = "Buy",
_buy,
_sell
),
BLANK()
)
)

  • ViralPatel212's avatar
    ViralPatel212
    Resolver I
    2 years ago

    amitchandak That did not work I tried the above in a measure and it returns a blank

    Also i did add it to the main measure and it didnt work. (added it to the last if statement. not sure if its placed in the correct area) see below. the result returns the same as the card image above

     

     MV Size Concat =

    var _ccy = SELECTEDVALUE('glimpse-prod-db output_file_dtl'[currency])
    var _iscapped = sum('glimpse-prod-db output_file_dtl'[is_capped])
    var _size_uncapped = SUM('glimpse-prod-db output_file_dtl'[size_in_MM_actual])
    var _size_capped = SUM('glimpse-prod-db output_file_dtl'[size_in_MM_capped_num])
    var _TotalEUR = if( [MV Size Actual EUR] = 0,"0",
    "EUR" & " " & SWITCH(TRUE(),
    [MV Size Actual EUR] < 1,FORMAT(ROUND([MV Size Actual EUR],2),"#,#,0.00") & "MM"
    ,[MV Size Actual EUR] >= 1 && [MV Size Actual EUR] < 1000,FORMAT(ROUND([MV Size Actual EUR],2),"#,#,#.00") & "MM"
    ,[MV Size Actual EUR] >= 1000 && [MV Size Actual EUR] < 1000000,FORMAT(ROUND([MV Size Actual EUR]/1000,2),"#,#,#.00") & "B"
    ,[MV Size Actual EUR] >= 1000000,FORMAT(ROUND([MV Size Actual EUR]/1000000,2),"#,#,#.00") & "T"

    )
    )

    var _buy =
    CALCULATE([BV Size Buy], 'glimpse-prod-db output_file_dtl'[side] ="Buy")

    var _sell =
    CALCULATE([BV Size Buy], 'glimpse-prod-db output_file_dtl'[side] ="Sell")
    var _total = _buy + _sell


    return

    IF([currency count] >= 43,_TotalEUR & " (Third Statement)",
    IF([currency count] > 1,
    IF(
        _size_uncapped > 0,
        "EUR" & " " &
        SWITCH(
            TRUE(),
             _iscapped <= 1,
            IF(
               _iscapped < 1,
                FORMAT([MV Size Actual EUR], "#,#,#0.00") & "MM",
                FORMAT([MV Size Actual EUR], "#,#,#0.00") & "MM+"
            ),


            _iscapped < 2,
            IF(
                _size_capped < 1,
                FORMAT([MV Size Actual EUR], "#,#,#0.00") & "MM+",
                FORMAT([MV Size Actual EUR], "#,#,#0.00") & "MM+"
            ),
            _iscapped > 1,
            SWITCH(
                TRUE(),
                _size_uncapped < 1, FORMAT([MV Size Actual EUR], "#,#,0.00"),
                _size_uncapped >= 1 && [MV Size Actual EUR] < 1000, FORMAT([MV Size Actual EUR], "#,#,#0.00") & "MM",
                _size_uncapped >= 1000 && [MV Size Actual EUR] < 1000000, FORMAT(ROUND([MV Size Actual EUR] / 1000, 2), "#,#,#0.00") & "B",
                _size_uncapped >= 1000000, FORMAT(ROUND([MV Size Actual EUR] / 1000000, 2), "#,#,#00.0") & "T"
            )
        )  
    ) & " (Second statement)",
    IF([currency count] =1,
    IF(HASONEFILTER('glimpse-prod-db output_file_dtl'[side]),
    IF(
        _size_uncapped > 0,
        _ccy & " " &
        SWITCH(
            TRUE(),
             _iscapped <= 1,
            IF(
               _iscapped < 1,
                FORMAT(_total, "#,#,#0.00") & "MM",
                FORMAT(_total, "#,#,#0.00") & "MM+"
            ),


            _iscapped < 2,
            IF(
                _size_capped < 1,
                FORMAT(_total, "#,#,#0.00") & "MM+",
                FORMAT(_total, "#,#,#0.00") & "MM+"
            ),
            _iscapped > 1,
            SWITCH(
                TRUE(),
                _size_uncapped < 1, FORMAT(_total, "#,#,0.00"),
                _size_uncapped >= 1 && _total < 1000, FORMAT(_size_uncapped, "#,#,#0.00") & "MM",
                _size_uncapped >= 1000 && _total < 1000000, FORMAT(ROUND(_size_uncapped / 1000, 2), "#,#,#0.00") & "B",
                _size_uncapped >= 1000000, FORMAT(ROUND(_total / 1000000, 2), "#,#,#00.0") & "T"
            )
        )  
    )
    & " (First statement)"))



    )
    )