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.
Hi all,
I have two tables: Table X and Table Y.
I have one column in Table X: GBR_ENT (ALL OF THEM ARE UNIQUE)
I have two columns in Table Y: GBR_ENT(NONE OF THEM ARE UNIQUE) and Bedrag (These are the mutated amounts for the GBR_ENT).
So I created a relationship between table X and Table Y on GBR_ENT. Now i have created groups in Table X, by using the function "New Group" on GBR_ENT. (See the picture below)
Now i want to subtract the values of these groups like below:
Subtotal: (02. Kostprijs omzet - 03. Salarissen)
So i want to see the "bedrag" by the row "Subtotal".
Can you guys help me out?
Solved! Go to Solution.
Hi @Anonymous,
Do you want to calculate the difference every two groups? If so, please add a calculated column in Table X first.
Group Index = RIGHT(LEFT('Table X'[GBR_ENT (groups)],2),1)
Then, create measures:
Bedrag current group = CALCULATE ( SUM ( 'Table Y'[Bedrag] ), ALLEXCEPT ( 'Table X', 'Table X'[GBR_ENT (groups)] ) ) Bedrag next group = CALCULATE ( SUM ( 'Table Y'[Bedrag] ), FILTER ( ALLSELECTED ( 'Table X' ), 'Table X'[Group Index] = SELECTEDVALUE ( 'Table X'[Group Index] ) + 1 ) ) diff between groups = [Bedrag current group]-[Bedrag next group]
Best regards,
Yuliana Gu
Hi @Anonymous,
It is not available to show the value (02. Kostprijs omzet - 03. Salarissen) in "SubTotal" row. While you can get this value via measure, similar to:
Diff = CALCULATE ( SUM ( 'Table Y'[Bedrag] ), FILTER ( ALLSELECTED ( 'Table X' ), 'Table X'[GBR_ENT(groups)] = "02. Kostprijs omzet" ) ) - CALCULATE ( SUM ( 'Table Y'[Bedrag] ), FILTER ( ALLSELECTED ( 'Table X' ), 'Table X'[GBR_ENT(groups)] = "03. Salarissen" ) )
Best regards,
Yuliana Gu
Hi @v-yulgu-msft,
It seems like it's working. But what should i do when i have:
"02. Kostprijs Omzet"
"03. Salarissen"
(02. Kostprijs Omzet - 03. Salarissen) => your dax-code
And what if i have
"02. Kostprijs Omzet" (value=500)
"03. Salarissen" (value=1000)
(02. Kostprijs Omzet - 03. Salarissen) => your dax-code (value=1000-500 = 500)
"04. Fee" (value=850)
"05. Facility" (value= 150)
"06. ABC" (value=200)
MEASURE?
Now i want to to see the all the orginal groups with the value but I also want to use just 1 calcutatio so i can distract the groups 06. ABC - 05. Facility - 04. Fee and your dax-code?
Can you help me out?
Hi @Anonymous,
Do you want to calculate the difference every two groups? If so, please add a calculated column in Table X first.
Group Index = RIGHT(LEFT('Table X'[GBR_ENT (groups)],2),1)
Then, create measures:
Bedrag current group = CALCULATE ( SUM ( 'Table Y'[Bedrag] ), ALLEXCEPT ( 'Table X', 'Table X'[GBR_ENT (groups)] ) ) Bedrag next group = CALCULATE ( SUM ( 'Table Y'[Bedrag] ), FILTER ( ALLSELECTED ( 'Table X' ), 'Table X'[Group Index] = SELECTEDVALUE ( 'Table X'[Group Index] ) + 1 ) ) diff between groups = [Bedrag current group]-[Bedrag next group]
Best regards,
Yuliana Gu
Hi, i think you are looking for Related or RelatedTable.
Check this out.
Regards
Happy to help!
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |