Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 @v-shex-msft ,
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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |