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 all,
how to create a measure which will give me the sum of amount column based on multiple types.
Type | Amount |
012200 | 206148.6 |
030100 | 62136 |
031100 | 7895 |
032010 | 105835.8 |
102000 | 5000 |
102000 | -500 |
102900 | -5000 |
102900 | 500 |
140002 | 52.5 |
140002 | 1973.75 |
140230 | 12663.94 |
220000 | -262.5 |
220000 | -9868.75 |
221000 | -50655.7 |
240240 | -12663.9 |
401400 | 210 |
430000 | -206149 |
431200 | -167972 |
650000 | 0 |
660000 | 50655.74 |
999990 | 0 |
For example, I want to sum all values where type begins with 4* or where types in (400000 till 499999 maybe this will be better).
I tried with manually typing every type like Four = CALCULATE(sum(Table[Amount]);filter(Table;Table[Type] = "401400" || Table[Type] = "430000")) but this is not good when I have table with 100 different types of 4.
Best regards,
Renato Fajdiga.
If Type Column is Text you can use Value to transform in Number
SUMWITH4 = CALCULATE ( SUM ( Table1[Amount] ), FILTER ( Table1, VALUE ( Table1[Type] ) >= 400000 && VALUE ( Table1[Type] ) <= 499999 ) )
You could try using filters on the right pane (Page level, Report level). You can define interval of Type, and when Type column is duplicated with data type text, then you also can filter as starts with 43, end with, contains etc.
And then simply have sum measure of amount column.
But unfortunately I think you can't use any easy filter directly on the page screen of PBI to this purpose, just only these filters on the right pane.
Regards.
Pavel
That's partly correct but I need all values for field type which starts with 2, 4 and 6. On Visual filters I have only options to put 2 filters one or (and) another.
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |