Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]))))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |