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

Hi so I checked and there is nothing be filtered on either the cards or the page to cause the difference between the two when in card form vs being in the matrix. So the point of the cards is to highlight at a high level the number of new customers and quotes that  they have for a given time frame determines by the date chiclets. A new customer has the status of A, and the customer creation date occurs during the same month as the what is being filtered for on the page then that customer counts as a new customer and I want to count the  number of quotes assoicated wtih them. It took me a lot of reserach to get a function that worked. How do you suggest I modify the function?

 

 

CALCULATE(DISTINCTCOUNT('Quotes Estimates US'[Customer]),FILTER(ALLSELECTED('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
Icon for Resident Rockstar rankResident 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
      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