Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Count control by Date Slicer

Hi Experts,    Please find the PBIX Download Here  There is a measure to calculate "Count", "Units", "Sales", "Margin", the values will be changed by the Date slicer  Value by Class = VAR curr...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Anonymous , count calculation is wrong

    Try

    Value by Class = 

VAR currentClassTable = FILTER(ALLSELECTED('Sales table'[Product]), [Class] in values('Pareto classification'[Product class]))

RETURN 
 SWITCH (SELECTEDVALUE('P&L'[P&L]),
 "Count",  SUMX(currentClassTable, CALCULATE(DISTINCTCOUNT('Sales table'[Product])))// COUNTROWS(currentClassTable)
 , "Units" , SUMX(currentClassTable, CALCULATE(SUM('Sales table'[Units])))
 , "Sales" , SUMX(currentClassTable, CALCULATE(SUM('Sales table'[Sales])))
, "Margin", SUMX(currentClassTable, CALCULATE(SUM('Sales table'[Margin]))))
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Anonymous , Try these

VAR currentClassTable = FILTER(ALLSELECTED('Sales table'[Product]), [Class] in values('Pareto classification'[Product class]))

or

VAR currentClassTable = FILTER(ALLSELECTED('Sales table'[Product]), [Class] in allselected('Pareto classification'[Product class]))

 

but if they are related table you do not need to do this

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    amitchandak Thanks so much! However it doesn't work, the results are as below. 

     

     

    VAR currentClassTable = FILTER(ALLSELECTED('Sales table'[Product]), [Class] in values('Pareto classification'[Product class]))

    VAR currentClassTable = FILTER(ALLSELECTED('Sales table'[Product]), [Class] in allselected('Pareto classification'[Product class]))

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      Anonymous , count calculation is wrong

      Try

      Value by Class = 

VAR currentClassTable = FILTER(ALLSELECTED('Sales table'[Product]), [Class] in values('Pareto classification'[Product class]))

RETURN 
 SWITCH (SELECTEDVALUE('P&L'[P&L]),
 "Count",  SUMX(currentClassTable, CALCULATE(DISTINCTCOUNT('Sales table'[Product])))// COUNTROWS(currentClassTable)
 , "Units" , SUMX(currentClassTable, CALCULATE(SUM('Sales table'[Units])))
 , "Sales" , SUMX(currentClassTable, CALCULATE(SUM('Sales table'[Sales])))
, "Margin", SUMX(currentClassTable, CALCULATE(SUM('Sales table'[Margin]))))
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        amitchandak  It's work now, many thanks