cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
electrobrit
Post Patron
Post Patron

aggregations are not correct across dates

I have 2 tables, one with monthly revenue and another with hard coded revenue by month (Committed and Budget)
I feel like it's something to do with the dates (and relationships) but I can't figure it out.

One table with the key revenue, I have Total measures based on a classication (Total likely, Total Qual or Total unqual) and want it to be a monthly spread (which works with one date)
Then the second part is the committed and budget (seperate table) where it's a monthly spread as well and works when using another date field.

I've created a relationship with the date tables but the committed and budget aggregate the total and show every month then the other totals don't show correctly.

electrobrit_0-1669922564836.png

electrobrit_1-1669922590210.png

Ultimately, I want the 2 combined and correct. Can someone help with advice on how to set this up to show correctly? I've been going in circles on the relationships. (I have more on the real report so can't lose the relationship with the New Opp and Date table)

electrobrit_2-1669922704497.png

 

here is a sample pbix


Thank you!

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @electrobrit ;

You could modify it.

Total Likely = 
IF(HASONEVALUE('Committed (2)'[Month Year]),
CALCULATE(SUM('NEW OPP'[Key Revenue]), FILTER('NEW OPP', 'NEW OPP'[QualGroup]="Order Book"&&EOMONTH([Date],-1)+1=MAX('Committed (2)'[Month Year]))),
CALCULATE(SUM('NEW OPP'[Key Revenue]), FILTER('NEW OPP', 'NEW OPP'[QualGroup]="Order Book")))
Total Qual = 
IF(HASONEVALUE('Committed (2)'[Month Year]),
CALCULATE(SUM('NEW OPP'[Key Revenue]), FILTER('NEW OPP', 'NEW OPP'[QualGroup]="Qualified"&&EOMONTH([Date],-1)+1=MAX('Committed (2)'[Month Year]))),
CALCULATE(SUM('NEW OPP'[Key Revenue]), 'NEW OPP'[QualGroup]="Qualified"))
Total Unqual = 
IF(HASONEVALUE('Committed (2)'[Month Year]),
CALCULATE(SUM('NEW OPP'[Key Revenue]), FILTER('NEW OPP', 'NEW OPP'[QualGroup]="Unqualified"&&EOMONTH([Date],-1)+1=MAX('Committed (2)'[Month Year]))),
CALCULATE(SUM('NEW OPP'[Key Revenue]),  'NEW OPP'[QualGroup]="Unqualified"))

Then create a new table.

vyalanwumsft_0-1669948123754.png

Then create a measure.

Measure = 
IF(ISINSCOPE('Table'[New]),
SWITCH(MAX('Table'[New]),
"Committed",SUM('Committed (2)'[Committed]),
"Budget",SUM('Committed (2)'[Budget]),
"Total Likely",[Total Likely],
"Total Qual",[Total Qual],
"Total Unqual",[Total Unqual]),
SUM('Committed (2)'[Committed])+SUM('Committed (2)'[Budget])+[Total Likely]+[Total Qual]+[Total Unqual])

The final show:

vyalanwumsft_1-1669948155703.png


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-yalanwu-msft
Community Support
Community Support

Hi, @electrobrit ;

You could modify it.

Total Likely = 
IF(HASONEVALUE('Committed (2)'[Month Year]),
CALCULATE(SUM('NEW OPP'[Key Revenue]), FILTER('NEW OPP', 'NEW OPP'[QualGroup]="Order Book"&&EOMONTH([Date],-1)+1=MAX('Committed (2)'[Month Year]))),
CALCULATE(SUM('NEW OPP'[Key Revenue]), FILTER('NEW OPP', 'NEW OPP'[QualGroup]="Order Book")))
Total Qual = 
IF(HASONEVALUE('Committed (2)'[Month Year]),
CALCULATE(SUM('NEW OPP'[Key Revenue]), FILTER('NEW OPP', 'NEW OPP'[QualGroup]="Qualified"&&EOMONTH([Date],-1)+1=MAX('Committed (2)'[Month Year]))),
CALCULATE(SUM('NEW OPP'[Key Revenue]), 'NEW OPP'[QualGroup]="Qualified"))
Total Unqual = 
IF(HASONEVALUE('Committed (2)'[Month Year]),
CALCULATE(SUM('NEW OPP'[Key Revenue]), FILTER('NEW OPP', 'NEW OPP'[QualGroup]="Unqualified"&&EOMONTH([Date],-1)+1=MAX('Committed (2)'[Month Year]))),
CALCULATE(SUM('NEW OPP'[Key Revenue]),  'NEW OPP'[QualGroup]="Unqualified"))

Then create a new table.

vyalanwumsft_0-1669948123754.png

Then create a measure.

Measure = 
IF(ISINSCOPE('Table'[New]),
SWITCH(MAX('Table'[New]),
"Committed",SUM('Committed (2)'[Committed]),
"Budget",SUM('Committed (2)'[Budget]),
"Total Likely",[Total Likely],
"Total Qual",[Total Qual],
"Total Unqual",[Total Unqual]),
SUM('Committed (2)'[Committed])+SUM('Committed (2)'[Budget])+[Total Likely]+[Total Qual]+[Total Unqual])

The final show:

vyalanwumsft_1-1669948155703.png


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.