Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have a CALCULATED TABLE like this:
I need to create a sumx that will be calculated per row as follows:
SUMX(Value per Product/Sum(Value) per Group * Multiplier)
However I need that SUM(Value) per group to change on the selection. So in case I have everything selected for the first row I would get:
2/(2+3+7) *1,2 = 0,2
But in case only items A and B are selected I would get:
2/(2+3) *1,2 = 0,08
I'm stuck on that dynamic grouping. When everything is selected Allexcept(Table['Group']) is working fine for the denominator, but of course it's not changing upon selection. I was thinking of using summarize as variable, but I couldn't figure that out.
Could you please help me with the above? I would really appreciate the comments. I'm sorry I don't share the pbix, but that's just a tiny piece of a huge report that I cannot share.
Solved! Go to Solution.
Thank you for your feedback.
I am not sure whether I understood your question correctly, but please check the below screenshot and the sample pbix file's link down below.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @Jihwan_Kim,
Thank you for sharing that. However, I need to get the sumx of those result measures and from that one I cannot get it, correct? Also I will be presenting data per Group.
Do you think that there is a way to make it work for that scenario? I tried with:
SUMX(Data,IF (
HASONEVALUE ( Groups[Group] ),
( _numerator / _denominator ) * _multiplier
)
but it's all returned empty. I checked with the Countrows function and it's giving me the total number of groups instead of 1 per row even when I put it by product. The differences I have between your and mine data models are that: my table is calculated table (although the end structure is the same) and the value is a calculated column. All the relationships between data are the same as in your example.
Sorry for not being more specific at the beggining.
Regards,
Magda
Thank you for your feedback.
I am not sure whether I understood your question correctly, but please check the below screenshot and the sample pbix file's link down below.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @Jihwan_Kim,
I really appreciate your help on this. But could you please remove from the table the Product and check whether the sum per Group matches? For some reason for me that is where it crushes and I would like to make sure I'm not making any mistakes although I copied the solution 1:1.
Regards,
Magda
Please ignore the above comment. I know what to do.
Thank you so much for your help!
Result measure : =
VAR _numerator =
SUM ( Data[Value] )
VAR _denominator =
CALCULATE (
SUM ( Data[Value] ),
ALLSELECTED ( Data ),
VALUES ( Groups[Group] )
)
VAR _multiplier =
SUM ( Data[Multiplier] )
RETURN
IF (
HASONEVALUE ( Groups[Group] ),
( _numerator / _denominator ) * _multiplier
)
https://www.dropbox.com/s/m3kve85pgc0yogq/magdamar.pbix?dl=0
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
User | Count |
---|---|
54 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
82 | |
55 | |
39 | |
20 | |
12 |