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,
I have a query that pulls in our sales pipeline from Salesforce which contains many opportunities that can have multiple rows. Each opportuinity has a unique id.
What I am trying to do is create a calculated table that has the Opportunity IDs of all deals where the total deal value is >=10
I have come up with the following formula that nearly works, but for some reason not quite. There are a few deals that are missing, and I don't know why. I think that maybe my table formula isn't doing what I think it is. Any help guidance with either why my formula isn't working, or what a better formula would be will be greatly appreciated. Thank you.
Here is my formula
Pipe TCV >10m = SUMMARIZE( CALCULATETABLE( FILTER('2 Pipeline','2 Pipeline'[Pipe CV €M]>=10)), '2 Pipeline'[Opportunity ID])
Solved! Go to Solution.
Hi @Andshepch ,
That's because you filtered the row rather than the total of each Opportunity ID. Please try these two measures.
Pipe TCV >10m = SUMMARIZE ( CALCULATETABLE ( FILTER ( '2 Pipeline', CALCULATE ( SUM ( '2 Pipeline'[Pipe CV €M] ), ALLEXCEPT ( '2 Pipeline', '2 Pipeline'[Opportunity ID] ) ) >= 10 ) ), '2 Pipeline'[Opportunity ID] )
Table = SELECTCOLUMNS ( FILTER ( SUMMARIZE ( '2 Pipeline', '2 Pipeline'[Opportunity ID], "Total", SUM ( '2 Pipeline'[Pipe CV €M] ) ), [Total] >= 10 ), "Opportunity ID", [Opportunity ID] )
Best Regards,
Hi @Andshepch ,
That's because you filtered the row rather than the total of each Opportunity ID. Please try these two measures.
Pipe TCV >10m = SUMMARIZE ( CALCULATETABLE ( FILTER ( '2 Pipeline', CALCULATE ( SUM ( '2 Pipeline'[Pipe CV €M] ), ALLEXCEPT ( '2 Pipeline', '2 Pipeline'[Opportunity ID] ) ) >= 10 ) ), '2 Pipeline'[Opportunity ID] )
Table = SELECTCOLUMNS ( FILTER ( SUMMARIZE ( '2 Pipeline', '2 Pipeline'[Opportunity ID], "Total", SUM ( '2 Pipeline'[Pipe CV €M] ) ), [Total] >= 10 ), "Opportunity ID", [Opportunity ID] )
Best Regards,
Perfect! Thank you.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |