Being able to use the sumx feature along with filtering to establish totals

[Anonymous]

I have several line items each with an ID on them and these lines are broken down into groups. I need a formula that will sum the total number of ID's in each group...while being filtered at the same time. Below is the current formula/formulas I'm trying to use.

Planned = CALCULATE(COUNTX(Ideas,Ideas[IdeaId]),FILTER(Ideas,Ideas[IdeaStatus] = "Approved"||Ideas[IdeaStatus] = "Closed"),FILTER('Timing Lookup','Timing Lookup'[Timing] = "2015-Q3"))

Planned = CALCULATE(SUMX(Ideas,Ideas[IdeaId]),FILTER(Ideas,Ideas[IdeaStatus] = "Approved"||Ideas[IdeaStatus] = "Closed"),FILTER('Timing Lookup','Timing Lookup'[Timing] = "2015-Q3"))

The sumx function formula produces an error that says "The function SUMX cannot work with values of type String."

Right now the calculate(countx formula is returning the total as a whole "43"... When I need each group to have a total that add's up to 43...

I don't know what formula I need to produce a group level total based off of the filters.

Any help would be greatly appreciated.

[v-huizhn-msft]

Hi @Anonymous,

SUMX function is only used to number type. You want the total row in table/matrix visuals displays correctly, right? If it is, please try the following formula and check if it works fine. If there is a relationship between 'Ideas' and 'Timing Lookup' tables.

Planned = SUMX(Ideas,CALCULATE(COUNTX(Ideas,Ideas[IdeaId]),FILTER(Ideas,Ideas[IdeaStatus] = "Approved"||Ideas[IdeaStatus] = "Closed"),FILTER('Timing Lookup','Timing Lookup'[Timing] = "2015-Q3")))

If this still does not resolve your issue, could you please share some sample table for further analysis? You can create similar fake data if your data is private.

Best Regards,
Angelia

[Anonymous]

Hi @v-huizhn-msft,

Unfortuntaley this did not acheive the results I would like. I've attached a photo of an Excel Mockup that I hope better explains. Timing and other columns are in other tables within power bi but for the example they are all grouped together

So basically I want the formula to count the number of Idea Ideas that occur and have the count appear by Group. So according to the formula I would like it to filter by "Approved" and "Closed" idea status and Timing "2015-Q3"

The picture above shows how I would like for it to appear... According to the data Finance and HR would both populate 1 as shown on the bottom half of the picture. I feel like this formula

Planned = SUMX(Ideas,CALCULATE(COUNTX(Ideas,Ideas[IdeaId]),FILTER(Ideas,Ideas[IdeaStatus] = "Approved"||Ideas[IdeaStatus] = "Closed"),FILTER('Timing Lookup','Timing Lookup'[Timing] = "2015-Q3")))

is very close. My apologies if this explanation/picture doesn't work for you and please let me know if I need to provide more detail. Thanks for the help!

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

When you post the sample data, you'd better shown it in table rather than screenshot, so that we can copy and reproduce your scenario. I test using your sample table, get expected result as follows.

Your Sample table
Create a measure using the formula.

Planned = CALCULATE(COUNTX(Ideas,Ideas[Ideas ID]),FILTER(Ideas,Ideas[Ideas Status] = "Approved"||Ideas[Ideas Status] = "Closed"),FILTER(Ideas,Ideas[Timing] = "2015-Q3"))+0

Finally, create a table visual, select the Ideas[Group] and measure as values, you will get expected result.



Best Regards,
Angelia