Forum Discussion
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 currentClassTable = FILTER(ALLSELECTED('Sales table'[Product]), [Class]= SELECTEDVALUE('Pareto classification'[Product class]))
RETURN
SWITCH (SELECTEDVALUE('P&L'[P&L]),
"Count", 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]))))For example below, the Circled Bottom Products is expected to 3, but I cannot do that, would anyone help, many thanks
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]))))
4 Replies
- amitchandak
Super User
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
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
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]))))