Sum total based on filter parameters

[ViralPatel212]

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 Total value.

2) IF 2 or more currency is selected than show EUR Converted total value.

3) IF 43 or more currency is selected show total EUR converted value.

There is more paramaters that go inisde.

Problem here is that the the measure works for total. but when i add a filter on to the card to show either Buy or Sell it doesnt work how i predicted.  Here is how is gets resulted.

As you can see works correctly where it only shows the buy Value but it ignores the first statement where if 2 currency are selected than it should show the EUR converted value: EUR 0.51MM and not PHP 215MM

Please could you see where i am going wrong?

Here are the measure used:

currency count =

COUNTROWS(SUMMARIZE('glimpse-prod-db output_file_dtl','glimpse-prod-db output_file_dtl'[currency]))

MV Size Actual EUR =

//SUMX('glimpse-prod-db output_file_dtl','glimpse-prod-db output_file_dtl'[size_in_eur])

SUM('glimpse-prod-db output_file_dtl'[size_in_eur])

 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(

    _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)"))

)

[amitchandak · Accepted solution]

@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]

@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)"))

)

)