Forum Discussion
Helper IIIALL() giving performance issues
Community SupportTotal Split - BAE_BLS All (Fixed) =
VAR Yr =
SELECTEDVALUE ( 'Base Year'[Year] )
-- Store values to be reused inside TREATAS
VAR TrtValues = VALUES ( 'AllocationKey Man.Fee'[Entitycode] )
-- Net Sales Total
VAR full =
CALCULATE (
[Amount Adjusted],
DimScenarioDetailed[Scen_Level1] = "FA",
'Date'[Year] = Yr,
DimAccountDetailed[Net Sales Total] = 1,
TREATAS ( TrtValues, DimEntity[Entity_flat_NodeName] ),
ALL ()
)
VAR BLS =
CALCULATE (
CALCULATE (
[Amount Adjusted],
DimScenarioDetailed[Scen_Level1] = "FA",
'Date'[Year] = Yr,
DimAccountDetailed[Net Sales Total] = 1,
TREATAS ( TrtValues, DimEntity[Entity_flat_NodeName] ),
ALL ()
),
'AllocationKey Man.Fee'[BA-Allocation] = "BAE_BLS"
)
VAR NetResult =
DIVIDE ( BLS, full ) * Factor[Net sales factor] -- Net Sales Section
-- Last date of year
VAR lastD =
CALCULATE (
LASTDATE ( 'Fact'[Date] ),
FILTER ( ALL ( 'Date' ), 'Date'[Year] = Yr )
)
-- Balance Sheet Section
VAR balancesheet =
CALCULATE (
[Amount Adjusted],
DimScenarioDetailed[Scen_Level1] = "FA",
DimAccountDetailed[BalanceSheet] = 1,
TREATAS ( TrtValues, DimEntity[Entity_flat_NodeName] ),
ALL (),
'Date'[Date] = lastD
)
VAR balan =
CALCULATE (
CALCULATE (
[Amount Adjusted],
DimScenarioDetailed[Scen_Level1] = "FA",
DimAccountDetailed[BalanceSheet] = 1,
TREATAS ( TrtValues, DimEntity[Entity_flat_NodeName] ),
ALL (),
'Date'[Date] = lastD
),
'AllocationKey Man.Fee'[BA-Allocation] = "BAE_BLS"
)
VAR Bal =
DIVIDE ( balan, balancesheet ) * Factor[Prod.asset factor]
-- P&L Section
VAR profit =
CALCULATE (
[Amount Adjusted],
DimScenarioDetailed[Scen_Level1] = "FA",
'Date'[Year] = Yr,
DimAccountDetailed[P&L] = 1,
TREATAS ( TrtValues, DimEntity[Entity_flat_NodeName] ),
ALL ()
)
VAR prof =
CALCULATE (
CALCULATE (
[Amount Adjusted],
DimScenarioDetailed[Scen_Level1] = "FA",
'Date'[Year] = Yr,
DimAccountDetailed[P&L] = 1,
TREATAS ( TrtValues, DimEntity[Entity_flat_NodeName] ),
ALL ()
),
'AllocationKey Man.Fee'[BA-Allocation] = "BAE_BLS"
)
VAR ProResult =
DIVIDE ( prof, profit ) * Factor[Conv.cost factor]
-- Final Result
VAR result = NetResult + Bal + ProResult
RETURN
result
Hope this helps !!
Helper IIIHi, when I incorporate your solution my number changes, so I guess the evaluation of the variable in the context is different from the evaluation in the part of the function itself.
BR,
Frank
- v-aatheeque
Hi fjjpeeters1976
Thanks for getting back!To help us understand the issue better, could you let us know which part of the calculation seems off is it the NetResult, Bal, or ProResult? Also, are you testing the formula in a specific visual like a matrix or card, or looking at it from a total level?
Let us know if there are any filters applied, such as year, entity, or scenario. Lastly, if possible, please share what result you were expecting and what value you’re seeing instead.
This will help us verify whether it’s a context evaluation issue or something specific to your data model.
- fjjpeeters1976
Hi, thanks for your reply.
I put my old and the new measures on a card visual next to eachother to make sure they all have the same filters applied to them. There are filters on year, entity and some other criteria, but they are same for both.
Comparing the three parts of the calculation. In the new calculation logic both netresult and proresult are giving me exactly 25% which is equal to both factors that I multiply with (25%). So it feels like in the new situation the denominator and the numerator are the same number ending up at value 1 which is multiplied than with the factor.Factor[Net sales factor]Factor[Conv.cost factor]In the correct version the netresult should end up at 11% and the proresult at 10.28% totalling to 21.28%.
Regarding the balancesheet part, I have an issue with the uploaded datapart so both of them are giving me a blank value at the moment, but I assume this is more a dataload issue than a DAX issue. When the upload is corrected I can check this part as well.Hope this clarifies it a bit. Love to hear your thoughts. Due to the size and confidentiality of the data I unfortunately cannot provide you with a data-extract.
BR,
Frank
- v-aatheeque
Total Split - BAE_BLS All (Fixed) =
VAR Yr = SELECTEDVALUE('Base Year'[Year])-- Entities used for TREATAS
VAR TrtValues = VALUES('AllocationKey Man.Fee'[Entitycode])-- Net Sales Section
VAR full =
CALCULATE(
[Amount Adjusted],
DimScenarioDetailed[Scen_Level1] = "FA",
DimAccountDetailed[Net Sales Total] = 1,
'Date'[Year] = Yr,
TREATAS(TrtValues, DimEntity[Entity_flat_NodeName])
)VAR BLS =
CALCULATE(
[Amount Adjusted],
DimScenarioDetailed[Scen_Level1] = "FA",
DimAccountDetailed[Net Sales Total] = 1,
'Date'[Year] = Yr,
TREATAS(TrtValues, DimEntity[Entity_flat_NodeName]),
'AllocationKey Man.Fee'[BA-Allocation] = "BAE_BLS"
)VAR NetResult = DIVIDE(BLS, full) * Factor[Net sales factor]
-- Balance Sheet Section
VAR lastD =
CALCULATE(
LASTDATE('Fact'[Date]),
FILTER(ALL('Date'), 'Date'[Year] = Yr)
)VAR balancesheet =
CALCULATE(
[Amount Adjusted],
DimScenarioDetailed[Scen_Level1] = "FA",
DimAccountDetailed[BalanceSheet] = 1,
TREATAS(TrtValues, DimEntity[Entity_flat_NodeName]),
'Date'[Date] = lastD
)VAR balan =
CALCULATE(
[Amount Adjusted],
DimScenarioDetailed[Scen_Level1] = "FA",
DimAccountDetailed[BalanceSheet] = 1,
TREATAS(TrtValues, DimEntity[Entity_flat_NodeName]),
'Date'[Date] = lastD,
'AllocationKey Man.Fee'[BA-Allocation] = "BAE_BLS"
)VAR Bal = DIVIDE(balan, balancesheet) * Factor[Prod.asset factor]
-- P&L Section
VAR profit =
CALCULATE(
[Amount Adjusted],
DimScenarioDetailed[Scen_Level1] = "FA",
DimAccountDetailed[P&L] = 1,
'Date'[Year] = Yr,
TREATAS(TrtValues, DimEntity[Entity_flat_NodeName])
)VAR prof =
CALCULATE(
[Amount Adjusted],
DimScenarioDetailed[Scen_Level1] = "FA",
DimAccountDetailed[P&L] = 1,
'Date'[Year] = Yr,
TREATAS(TrtValues, DimEntity[Entity_flat_NodeName]),
'AllocationKey Man.Fee'[BA-Allocation] = "BAE_BLS"
)VAR ProResult = DIVIDE(prof, profit) * Factor[Conv.cost factor]
-- Final
RETURN
NetResult + Bal + ProResult
Hope this helps !!