Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculated Measure not working in the Matrix

So I created a calculated measure to count the number of new customers and new customer quotes each month. See logic below. I also have a matrix where I have all the sales people listed and I want to...
Need Help
  • JarroVGIT's avatar
    JarroVGIT
    6 years ago

    Whoops sorry:

    CALCULATE(DISTINCTCOUNT('Quotes Estimates US'[Customer]),FILTER('Quotes Estimates US','Quotes Estimates US'[Job Start Year] = YEAR('Quotes Estimates US'[Customer Created Date])&&'Quotes Estimates US'[Job Start Month] = MONTH('Quotes Estimates US'[Customer Created Date])&&'Quotes Estimates US'[Customer Status] = "A"))
v-kelly-msft's avatar
v-kelly-msft
Community Support
6 years ago

Hi Anonymous ，

 

If you wanna calculate in “customer status =A”, the related measure is as below:

 

 

Measure = COUNTROWS(FILTER(SUMMARIZE(FILTER(ALLSELECTED('Quotes Estimates US'),'Quotes Estimates US'[Customer Status]="A"),'Quotes Estimates US'[Customer],"MinDate",FORMAT(MIN('Quotes Estimates US'[Customer Created Date]),"MMM YY")), [MinDate] in FILTERS(Calender[Month Year])))

 

 

 

If you wanna calculate in all statuses, pls see below:

 

 

Measure 2 = 
var c = MAX('Quotes Estimates US'[Job Start Year])
var prem = CALCULATE(MAX('Quotes Estimates US'[Job Start Year]),FILTER(ALLSELECTED('Quotes Estimates US'),'Quotes Estimates US'[Job Start Year]<c))
var predis = CALCULATETABLE(DISTINCT('Quotes Estimates US'[Customer]),FILTER(ALLSELECTED('Quotes Estimates US'),'Quotes Estimates US'[Job Start Year] = prem),VALUES('Quotes Estimates US'[Customer Status]))
 
return
CALCULATE(DISTINCTCOUNT('Quotes Estimates US'[Customer]),FILTER('Quotes Estimates US',NOT('Quotes Estimates US'[Customer] in predis )))

 

 

For the related .pbix file ,pls click here.

 

Hope this would help.

 

Best Regards,

Kelly