Forum Discussion
Post ProdigyIgnore all slicers except for one
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 ) )
Super UserHi dokat ,
Let me correct my previous reply. I was thinking you were using a table as the second argument in divide but didnt realize it was a third argument. Either way, that would still return an error.
As per documentation, the alternate result when the division returns 0 must be a constant. The below formula will return the word error as the alternate result is a constant:
Test = DIVIDE(1,0, "error")
However, this will return an error as the alternate result is not a constant.
Test = DIVIDE(1,0, max(1,0))
You can use IF instead of using the alternate result argument which would be something like
=
IF (
[divide measure] = 0
|| ISBLANK ( [divide measure] ),
SELECTEDVALUE ( table[column] ),
[divide measure]
)
Post Prodigydanextian I am still cominga cross same issue and modified the code as below, Formula shouldnt be returning Test = DIVIDE(1,0, "error")...if there is value in numerator denominator should have have a value too. I think part of the issue is CY[Slicer} is in differet table. It is not in 'Output' table
test = 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])
)))
I
- danextian
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 )- dokat
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 ) )