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"))
JarroVGIT's avatar
JarroVGIT
Resident Rockstar
6 years ago

Like I suggested in my previous post, remove the interaction between the slicers and the Card visual except for the Year and Month slicers. Check this tutorial to see how to do that: https://docs.microsoft.com/en-us/power-bi/service-reports-visual-interactions

 

Then the formula is:

CALCULATE(DISTINCTCOUNT('Quotes Estimates US'[Customer]),'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")

 

That should work and fit your requirements. Please re-read my explanation on context as I don't think you fully grasped it yet. If you want a Card measure to be ignorant of your selection in Slicers, then give it a NEW context. 

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

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
    Resident 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
      Community 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