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 Team.
I appreciate the help in advance.
I would like to replicate a SUMIFS function in PBI. My sample data is shown below. In the example, my answer would be 6. I would like a new column to list the SUMIFS value for all rows in my real table. How can I achieve this in PBI? I am slightly familiar with the GROUP BY button in Power Query but I want to keep my original table in tact.
Thanks, FB
Solved! Go to Solution.
Hi @FOXYBARK , can you try this (calculated column):
sumif ex =
CALCULATE(SUM('Table'[Count]),FILTER('Table','Table'[Group] = EARLIER('Table'[Group]) && 'Table'[Party] = EARLIER('Table'[Party])))
Hi @FOXYBARK ,
Here I suggest you to create a measure as below.
How many from Group B,Party equal to Homecoming =
CALCULATE (
SUM ( 'Table'[Count] ),
FILTER (
'Table',
'Table'[Group] = "B"
&& 'Table'[Party] = "Homecoming"
)
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @FOXYBARK ,
Here I suggest you to create a measure as below.
How many from Group B,Party equal to Homecoming =
CALCULATE (
SUM ( 'Table'[Count] ),
FILTER (
'Table',
'Table'[Group] = "B"
&& 'Table'[Party] = "Homecoming"
)
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I need an entire column, not a measure. I hit Solved by mistake.
FB
[Your Column] = // calc column
// Don't use CALCULATE in calculated columns
// as this slows down calculations tremendously
// especially on big tables.
var vCurrentParty = T[Party]
var vCurrentGroup = T[Group]
var Output =
sumx(
filter(
T,
T[Party] = vCurrentParty
&&
T[Group] = vCurrentGroup
),
T[Count]
)
return
Output
Hi @FOXYBARK , can you try this (calculated column):
sumif ex =
CALCULATE(SUM('Table'[Count]),FILTER('Table','Table'[Group] = EARLIER('Table'[Group]) && 'Table'[Party] = EARLIER('Table'[Party])))
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 |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |