Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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]:
Solved! Go to Solution.
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
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:
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
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
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])
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
74 | |
71 | |
39 | |
27 | |
27 |
User | Count |
---|---|
97 | |
96 | |
58 | |
45 | |
42 |