Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi Community,
I want to create a measure which will calculate the price of my line items (opportunities) when:
Family_type = "Product" AND business_type_name="New" and Closed Pipeline="Open"
I tried the following but it never worked.
4Q TCV = CALCULATE(SUM('FACT_PIPELINE'[SalesPrice],IF(AND('FACT_PIPELINE'[Family]= "Product",'FACT_PIPELINE'[business_type_name]= "New",'FACT_PIPELINE'[Closed Pipeline]="Open")
Is there any way to achieve this?
Solved! Go to Solution.
Hi
Try this.
4Q TCV = CALCULATE(SUM(FACT_PIPELINE[SalesPrice]), FILTER (FACT_PIPELINE, FACT_PIPELINE[Family]= "Product"), FILTER (FACT_PIPELINE,FACT_PIPELINE[business_type_name]= "New"), FILTER (FACT_PIPELINE,'FACT_PIPELINE'[Closed Pipeline]="Open") )
Thanks
Raj
Hi
Try this.
4Q TCV = CALCULATE(SUM(FACT_PIPELINE[SalesPrice]), FILTER (FACT_PIPELINE, FACT_PIPELINE[Family]= "Product"), FILTER (FACT_PIPELINE,FACT_PIPELINE[business_type_name]= "New"), FILTER (FACT_PIPELINE,'FACT_PIPELINE'[Closed Pipeline]="Open") )
Thanks
Raj
I have been trying to utilize this same concept to sum all rows that meet multiple VAR critera, but its not quite working. What I am trying to do is a calculation of the last 4 weeks of sales. If I use only one variable I am able to bring data for only one week, but if i add another filter criteria to take into consideration an additional week it shows blank.
Any ideas?
@Anonymous hey hey! stumbled across this old thread and am using your recommendation below. It's been very helpful to me already -- thanks!!!
One other question -- can you show me how to make one of the filters an AND statement?
I've tried using && but can tell it wasn't quite the right placement. Basically just looking for some of my rows to have 2 criteria that are mandatory in order to be included in the SUM.
Thanks Raj! Answered a question of mine, too - your contribution is appreciated.
When using the CALCULATE function, you do not need to add the IF and AND functions. The CALCULATE function has filter syntax built in. CALCULATE(<measure expression>, <filter1>, <filter2>, ...)
So your statement should read:
4Q TCV = CALCULATE(SUM('FACT_PIPELINE'[SalesPrice]),'FACT_PIPELINE'[Family]= "Product",'FACT_PIPELINE'[business_type_name]= "New",'FACT_PIPELINE'[Closed Pipeline]="Open")
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |