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.
HELP!
My lower level items (and subtotals) are displayed correctly, but higher levelsubtotals do not respect the negative sign of some lower level correctly.
How do I get the subtotal on TRANSPORT to be the sum of "NL_TENNET_N -> BE_ELIA_N" and "BE_ELIA_N -> NL_TENNET_N" with their sign correctly applied and displayed?
Please find at the link below a sample PBIX file to take a closer look:
https://drive.google.com/file/d/1gCbE6uP58TD5ZDnjAkRVhzocBGB0uRuk/view?usp=sharing
Example
As you can see, the TRANSPORT subtotal in not correct. It should be the sum of -290 + 130 = -160
One important detail:
The negative values under NL_TENNET_N -> BE_ELIA_N are being calculated to be negative in the measure by evaluating the [CONTROL_AREA] and [IN_AREA] fields. In the DB, they are positive. But I need to show them as negative values in the report and also incorporate them as such in the subtotal sums.
The DAX I use is
Value_signed_ImportExport_15min =
VAR ContractLine_Total =
if(and(SELECTEDVALUE(ContractLine[IN_AREA]) <> "", SELECTEDVALUE('Selectable_ControlAreas (for Combinations)'[CONTROL_AREA]) <> SELECTEDVALUE(ContractLine[IN_AREA])),
- SUMX(ContractLine, CALCULATE(SUM(TimeSeriesQuarterHour[VALUE_SIGNED]), ContractPosition[POSITION_TYPE] = "QUANTITY")),
SUMX(ContractLine, CALCULATE(SUM(TimeSeriesQuarterHour[VALUE_SIGNED]), ContractPosition[POSITION_TYPE] = "QUANTITY"))
)
VAR Direction_Total = SUMMARIZE(ContractLine, ContractLine[CONTRACT_LINE_ID], "ContractLineID_Total", ContractLine_Total)
RETURN
IF(HASONEVALUE(ContractLine[Grid Combination]),
ContractLine_Total,
SUMX(Direction_Total, [ContractLineID_Total]) / COUNTX(Direction_Total, [ContractLineID_Total])
)
I've been trying on this for days now but can't get it to work.
How do I get the subtotal on TRANSPORT to be the sum of "NL_TENNET_N -> BE_ELIA_N" and "BE_ELIA_N -> NL_TENNET_N" with their sign correctly applied and displayed?
(Btw: The categories under Transport could of course also be others, depending on the user's slicer choice and underlying data.)
Many thanks!
Jan
Stuff like this
SUMX(ContractLine, CALCULATE(SUM(TimeSeriesQuarterHour[VALUE_SIGNED]), ContractPosition[POSITION_TYPE] = "QUANTITY"))
should ring your warning bells. You either use a SUMX iterator or a CALCULATE, but not both. Unless you want to go all out crazy A Double CALCULATE Solves a SUMX Problem - Excelerator BI
Good morning @lbendlin
thanks for the link.
There is an interesting point int the post:
"Intermediate DAX users can be forgiven for thinking that Context Transition occurs because of the CALCULATE function [...] – but [...] it does not happen. Remember earlier in the post I explained that the second parameter of CALCULATE is the first part of the formula to be executed. So [...], the FILTER portion [...] is executed in the initial filter context without any context transition. The CALCULATE isn’t executed until after the FILTER portion is complete."
My guess is this is the problem in my case as well.
When the calculation of the subtotal on TRANSMISSION level happens, the filtering is done one the initial context, the one of the TRANSMISSION level.
However, in order to determine, if one needs to multiply the value by -1 or not, context of the lower levels is needed. Because it needs to be evaluated in THAT context, what the IN_AREA and OUT_AREA per CONTRACT_LINE_ID is and whether for this combination the sum must be negated.
So, e.g. in the case of the selected area of the Netherladsn (NL / NL_TENNET_N) the flows of NL_TENNET_N -> BE_ELIA_N must be multiplied by -1 while in the other direction, i.e. BE_ELIA_N -> NL_TENNET_N, the sum is taken as it is.
THIS context is apparently not present, and this is why for the subtotal of TRANSMISSION, the algorythm simply sums up all values of the underlying CONTRACT_LINE_IDs irrespective of their direction.
Still the riddle remains, how to make Power BI use the lower level context for the higher level evaluation?
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |