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"))
Anonymous's avatar
Anonymous
Not applicable
6 years ago

So the formula is giving me an error, where I have the customer created date it is wanting a measure not a field and the error is that the expression referes to multiple clolumns and multiple columns cannot converted to a scalar value.

JarroVGIT's avatar
JarroVGIT
Icon for Resident Rockstar rankResident Rockstar
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
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi JarroVGIT ,

     

    Sorry I just found an error in the measure, I have modified it, pls see below:

     

    1.First you need to create a new  calendar table, using calendarauto (),then create a calculated column, expression is as below:

    Ym = FORMAT('Calender'[Date],"mmm yyyy")

     

    2.Then  create a measure 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 YYYY")), [MinDate] in FILTERS(Calender[Ym])))

     

    Finally you will see :

     

     

    As for measure 2,it also needs to corrected, steps are as below:

     

    1.create a calculated column:

    Ym = 'Quotes Estimates US'[Job Start Year]*100+'Quotes Estimates US'[Job Start Month]

    2.Then create a measure:

     

    Measure 2 = 
var c = MAX('Quotes Estimates US'[Ym])
var prem = CALCULATE(MAX('Quotes Estimates US'[Ym]),FILTER(ALLSELECTED('Quotes Estimates US'),'Quotes Estimates US'[Ym]<c))
var predis = CALCULATETABLE(DISTINCT('Quotes Estimates US'[Customer]),FILTER(ALLSELECTED('Quotes Estimates US'),'Quotes Estimates US'[Ym] = 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 )))

     

    Finally you will see :

     

     

     

    For the related .pbix file,pls click here.

     

    Best Regards,

    Kelly