Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
as of now I have treid many things and tried to wrap my head around this issue but could not find a way to solve it.
Here's the problem:
I need to calculate the value assigned to an account based on the sign of the value. In case the value is negative, the value is assigned a node and in case it is positive it is assigned to another node. The measure is working fine, but the value on higher hierachy levels is not calculated as I would expect it.
In this example the value on ID = 2 is calculated correct via the sum_swing measure. But on node 110 the value should be -24 instead of -17 in column sum_swinging as ID 2 carries the value 0.
This is what I would expect as a result
These are the two tables representing the source data
The measure is defined as follows:
sum values = calculate(sum(Tabelle2[Value]))
sum_swinging =
VAR sum_ac =
SUM(Tabelle2[Value])
VAR indicator_swing =
OR(ISBLANK(SELECTEDVALUE(Tabelle1[NegIndicator])), ISBLANK(SELECTEDVALUE(Tabelle1[PosIndicator])))
VAR indicator_side =
OR(AND(ISBLANK(SELECTEDVALUE(Tabelle1[NegIndicator])), [sum values] > 0), AND(ISBLANK(SELECTEDVALUE(Tabelle1[PosIndicator])), [sum values] < 0))
Return
IF(indicator_swing, IF(indicator_side, sum_ac, 0), sum_ac)
What I did alread:
I found this blogpost (https://www.daxpatterns.com/parent-child-hierarchies/ ) but I do not think this is the right way of doing it. Also I found these two explanations but could not figure out how to do is (https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/ , https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-... )
Probably the solution is already close but I just cannot put the pieces together.
Cheers
F
Here's the example data:
Tabelle1
ID | L0 | L1 | L2 | L3 | L4 | NegIndicator | PosIndicator |
1 | 100 | 110 | 111 | 1 | X | X | |
2 | 100 | 110 | 112 | 2 | X | ||
3 | 100 | 120 | 3 | X | X | ||
2 | 200 | 210 | 211 | 213 | 2 | X | |
4 | 200 | 210 | 212 | 4 | X | X | |
5 | 200 | 220 | 5 | X | X |
Tabelle2
ID | Value |
1 | 50 |
2 | 46 |
3 | 11 |
4 | 43 |
5 | 77 |
6 | 26 |
1 | -74 |
2 | -39 |
3 | -53 |
4 | -14 |
5 | -72 |
6 | -30 |
Hi @I_got_question ,
Please allow me to ask questions and forgive me if I am wrong.
In your table 2, ID 1 to 6 have duplicate values, so how do you reference them?
For example ID1, if you want to get the value of ID1 in the table, is it 50 or -74?
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-stephen-msft ,
the table above is a simplification of the problem and the data used in the underlying case. The duplicates may be assigned to different timestamps thus I select them via a date slicer.
For the resulting matrix the selected values (also the multiple lines for one ID) shall simply be added up.
After some further research and testing I found a solution using a second measure that uses SUMX and builds a temporary table with the original measure. You can find the original post here:
https://finance-bi.com/blog/power-bi-totals-incorrect/
This is the resulting dax formula:
swinging_tempTable2 =
sumx(
SELECTCOLUMNS(Tabelle1, "L0", [L0], "L1", [L1], "L2", [L2], "L3", [L3], "L4", [L4]), Table_Measures[sum_swinging]
)
Although this leads to correct subtotals in the matrix hierarchy the performance is not very good for a larger hierarchy and facts table. Is there any idea on how to improve the measure to get a better performance?
User | Count |
---|---|
40 | |
26 | |
22 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |