Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
navafolk
Helper IV
Helper IV

If returns multiple values

Hi pros,

I my Transaction table is as following:

DateTypeAmount
01/01/2020local secured fund100
01/01/2020local unsecured fund50
01/01/2020global secure fund150
01/01/2020global unsecured fund100

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'

DateSum_typetotal
01/01/2020local fund150
01/01/2020global fund250

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.
1 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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

4 REPLIES 4
navafolk
Helper IV
Helper IV

Thank you guys @amitchandak and @Anonymous ,

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 😄

@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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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
Super User
Super User

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

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.