Forum Discussion
Ignore all slicers except for one
- 3 years ago
danextian Thanks for your response rather than using allexcept removefilters/values did the trick.
Output Fixed GP Rate = VAR _GP = CALCULATE ( ('Output'[Output TY]), REMOVEFILTERS ( 'Output' ), ValueS('CY'[SLICER]), 'Output'[P&L] = "Gross Profit" ) VAR _NS = CALCULATE ( ('Output'[Output TY]), REMOVEFILTERS ( 'Output' ), ValueS('CY'[SLICER]), 'Output'[P&L] = "Net Sales" ) RETURN IF ( ISBLANK ( DIVIDE ( _GP, _NS ) ) || DIVIDE ( _GP, _NS ) = 0, SELECTEDVALUE ( 'CY'[Slicer] ), DIVIDE ( _GP, _NS ) )
As previously mentioned, this will return a table:
FILTER ( ALL ( 'CY'[Slicer] ), 'CY'[Slicer] = SELECTEDVALUE ( 'CY'[Slicer] ) )
If you put this in a separate measure, you'll get something like:
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value. This alone will cause an error. SELECTEDVALUE ( 'CY'[Slicer] ) should have sufficed but even so, this will cause an error. As mentioned, the third argument in DIVIDE must be a constant. If you dissect the DIVIDE within your IF conditions, the lines in below below are still not constants - they're variables that are dependent on the value of the current filter contexts or other calculations - they should be a plain number or a text string.
=
IF (
DIVIDE (
CALCULATE (
( Output[Output TY] ),
ALLSELECTED ( 'Output' ),
'Output'[P&L] IN { "Gross Profit" }
),
CALCULATE (
( 'Output'[Output TY] ),
ALLSELECTED ( 'Output' ),
'Output'[P&L] IN { "Net Sales" }
),
FILTER ( ALL ( 'CY'[Slicer] ), 'CY'[Slicer] = SELECTEDVALUE ( 'CY'[Slicer] ) )
) = 0
|| ISBLANK (
DIVIDE (
CALCULATE (
( Output[Output TY] ),
ALLSELECTED ( 'Output' ),
'Output'[P&L] IN { "Gross Profit" }
),
CALCULATE (
( 'Output'[Output TY] ),
ALLSELECTED ( 'Output' ),
'Output'[P&L] IN { "Net Sales" }
),
FILTER ( ALL ( 'CY'[Slicer] ), 'CY'[Slicer] = SELECTEDVALUE ( 'CY'[Slicer] ) )
)
),
SELECTEDVALUE ( 'CY'[Slicer] ),
DIVIDE (
CALCULATE (
( Output[Output TY] ),
ALLSELECTED ( 'Output' ),
'Output'[P&L] IN { "Gross Profit" }
),
CALCULATE (
( 'Output'[Output TY] ),
ALLSELECTED ( 'Output' ),
'Output'[P&L] IN { "Net Sales" }
),
FILTER ( ALL ( 'CY'[Slicer] ), 'CY'[Slicer] = SELECTEDVALUE ( 'CY'[Slicer] ) )
)
)
Instead of writing this very log formula, I would have used variables instead or write those within calculate in a separate measure. I don't also see why use an IN operator when there's only one value in the list after it. { "Gross Profit" } - there is only "Gross Profit" inside the curly brackets, nothing else.
=
VAR _GP =
CALCULATE (
( Output[Output TY] ),
ALLSELECTED ( 'Output' ),
'Output'[P&L] = "Gross Profit"
)
VAR _NS =
CALCULATE (
( 'Output'[Output TY] ),
ALLSELECTED ( 'Output' ),
'Output'[P&L] = "Net Sales"
)
RETURN
IF (
ISBLANK ( DIVIDE ( _GP, _NP ) )
|| DIVIDE ( _GP, _NP ) = 0,
SELECTEDVALUE ( 'CY'[Slicer] ),
DIVIDE ( _GP, _NP )
)
or separate GROSS PROFIT and NET SALES mesures to be able to reuse them later
Gross Profit =
CALCULATE (
( Output[Output TY] ),
ALLSELECTED ( 'Output' ),
'Output'[P&L] = "Gross Profit"
)
Net Sales =
CALCULATE (
( Output[Output TY] ),
ALLSELECTED ( 'Output' ),
'Output'[P&L] = "Net Sales"
)
DIVIDE Measure =
VAR _DIV =
DIVIDE ( [Gross Profit], [Net Sales] )
RETURN
IF ( _DIV = 0 || ISBLANK ( _DIV ), SELECTEDVALUE ( 'CY'[Slicer] ), _DIV )
danextian Thanks for your response rather than using allexcept removefilters/values did the trick.
Output Fixed GP Rate =
VAR _GP =
CALCULATE (
('Output'[Output TY]),
REMOVEFILTERS ( 'Output' ),
ValueS('CY'[SLICER]),
'Output'[P&L] = "Gross Profit"
)
VAR _NS =
CALCULATE (
('Output'[Output TY]),
REMOVEFILTERS ( 'Output' ),
ValueS('CY'[SLICER]),
'Output'[P&L] = "Net Sales"
)
RETURN
IF (
ISBLANK ( DIVIDE ( _GP, _NS ) )
|| DIVIDE ( _GP, _NS ) = 0,
SELECTEDVALUE ( 'CY'[Slicer] ),
DIVIDE ( _GP, _NS )
)