Forum Discussion

ViralPatel212's avatar
ViralPatel212
Icon for Resolver I rankResolver 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 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)"))



)

 

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()
    )
    )

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper 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
      Icon for Resolver I rankResolver 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)"))



      )
      )