If returns multiple values

[navafolk]

Hi pros,

I my Transaction table is as following:

Date	Type	Amount
01/01/2020	local secured fund	100
01/01/2020	local unsecured fund	50
01/01/2020	global secure fund	150
01/01/2020	global unsecured fund	100

I would like to sum up Transaction to Summary table (expected as below) without creating any support column:

- Sum 'local secured fund' and 'local unsecured fund' to mother group 'local fund'

Date	Sum_type	total
01/01/2020	local fund	150
01/01/2020	global fund	250

I cannot use if function (if x is true, return to multiple values: a, b) to sumif in Summary[total]:

=CALCULATE(SUM('Transaction'[Amount] ),

   FILTER(ALL('Transaction'),

      'Transaction'[Date] ='Summary'[Date],

      &&'Transaction'[Type]=if('Summary'[Sum_type]="local fund",{"local unsecured fund","local secured fund"},'Daily schedule'[Account type])))

Any ideal? Please help, thank you very much.

[navafolk]

Thank you guys @amitchandak and @v-shex-msft ,

It would be great if we can calculate directly without supporting column/table.

I can calculate sum with IN statement: 

=CALCULATE(SUM('Transaction'[Amount] ),

   FILTER(ALL('Transaction'),

      'Transaction'[Type] IN {"local unsecured fund","local secured fund"}))

Is there any way that I can put this IN function into a conditional formual like: if true returns function IN {"local unsecured fund","local secured fund"}? I am not familiar with DAX syntax 😄

[amitchandak · Accepted solution]

@navafolk ,

if need two measure the try

global amount =
sumx(filter('Transaction',search("global",Table[Type],,0)>0),'Transaction'[Amount])

local amount =
sumx(filter('Transaction',search("local",Table[Type],,0)>0),'Transaction'[Amount])

Else check binning can help

https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[v-shex-msft]

Hi @navafolk,

I'd like to suggest you add a calculated column to extract category from type field, then you can simply use date value and new category column to create a table visual with aggregate amount values.

Category = 
VAR _start =
    LEFT ( [Type], SEARCH ( " ", [Type], 1, -1 ) - 1 )
VAR _end =
    RIGHT (
        [Type],
        LEN ( [Type] )
            - SEARCH ( " ", [Type], SEARCH ( " ", [Type], 1, -1 ) + 1, -1 )
    )
RETURN
    _start & " " & _end

Regards,

Xiaoxin Sheng

[amitchandak]

@navafolk ,

Try to create a new table like

New table =
summarize(selectcolumns(Table, "Date",Table[Date],"Sum_type" , if(search("global",Table[Type],,0)>0,"global fund","local fund"), "_total",Table[Amount]),[Date],[Sum_type], "Total",[_total])