Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
@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 , 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]))
@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]))))