Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
In the above image in column 2 want to calculate the % using the subtotal of the parental row
for example the subtotal is 11 and i want to calculate (2-11)/11
Solved! Go to Solution.
Hi @chkapelis ,
According to your description, you want to calculate (2-11)/11 in the subtotal of the parent term, can you describe in detail what the 2 is based on? You have three values of 2,4,5 in the sub term, how is the 2 determined? If it is the minimum value, please try this:
Measure =
var _a = SUM('Table'[Value])
var _b = CALCULATE(MIN('Table'[Value]),ALLEXCEPT('Table','Table'[ID]))
Return IF(ISINSCOPE('Table'[ID-2]),_a,DIVIDE((_b-_a),_a))
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @chkapelis ,
According to your description, you want to calculate (2-11)/11 in the subtotal of the parent term, can you describe in detail what the 2 is based on? You have three values of 2,4,5 in the sub term, how is the 2 determined? If it is the minimum value, please try this:
Measure =
var _a = SUM('Table'[Value])
var _b = CALCULATE(MIN('Table'[Value]),ALLEXCEPT('Table','Table'[ID]))
Return IF(ISINSCOPE('Table'[ID-2]),_a,DIVIDE((_b-_a),_a))
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Please try this:
Measure with subtotal =
IF(HASONEVALUE('Your table'[KOKOPOY]),
[Your measure],
FORMAT([Your division],"+0\ %;-0\ %;+0\ %")
)
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
84 | |
39 | |
25 | |
21 |