Forum Discussion
Aggregated Total based on Filter
This is the filter that I have.
This is my Chart.
I just want to get the Aggregated Total of all the Opportunity even when it has no filter or there's a filter.
I have done tying this Measures that came from this community, but still no luck.
Running Total Opps Generated =
CALCULATE (
COUNTROWS(
FILTER(Opportunities,
Opportunities[Current Stage] <> "Marketing Qualified Lead" &&
Opportunities[Current Stage] <> "Marketing Qualified Lead 10%" &&
Opportunities[Current Stage] <> "MQL 10%" &&
Opportunities[Current Stage] <> "Qualified Lead 10%" &&
Opportunities[Status] = "Open" &&
Opportunities[ISM Contribution] = "Marketing Generated"
)
),
FILTER (
ALL('Calendar'[Date]),
ISONORAFTER('Calendar'[Date], MAX ('Calendar'[Date]),DESC
)
), USERELATIONSHIP('Calendar'[Date], Opportunities[Date Created])
)Running Total Opps Generated =
CALCULATE (
COUNTROWS(
FILTER(Opportunities,
Opportunities[Current Stage] <> "Marketing Qualified Lead" &&
Opportunities[Current Stage] <> "Marketing Qualified Lead 10%" &&
Opportunities[Current Stage] <> "MQL 10%" &&
Opportunities[Current Stage] <> "Qualified Lead 10%" &&
Opportunities[Status] = "Open" &&
Opportunities[ISM Contribution] = "Marketing Generated"
)
),
FILTER (
ALL(Opportunity),
(Opportunity[Date Created]<= MAX(Opportunity[Date Created])
)
), USERELATIONSHIP('Calendar'[Date], Opportunities[Date Created])
)
Any thoughts or workaround about this?
Help would be greatly appreciated.
Thank you so much!
amitchandak parry2k PhilipTreacy
Hi Anonymous ,
Please check if this is what you want:
Measure = CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Month] <= MAX ( 'Calendar'[Month] ) ) )Best regards
Icey
If this post helps, then consider Accepting it as the solution to help other members find it faster.
3 Replies
- amitchandak
Super User
Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Hello amitchandak this is the sample data.
In Oct 2020 there is 1 row.
In Nov 2020 there is 2 row.
In Dec 2020 there is 2 row.
In Jan 2021 there is 1 row.
I want to show this kind of Chart that is responsive using a slicer.
Thank you.
- Icey
Community Support
Hi Anonymous ,
Please check if this is what you want:
Measure = CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Month] <= MAX ( 'Calendar'[Month] ) ) )Best regards
Icey
If this post helps, then consider Accepting it as the solution to help other members find it faster.
