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.
Hello,
I'm new in PowerBI so looking for some assist. Let's say I've table called DATA :
GL Account no. | Amount |
6001110 | -2663,67 |
5008310 | -2434,96 |
5008110 | -1530 |
6001310 | -1080 |
6001152 | -243,34 |
6008152 | -56,66 |
6008152 | 243,34 |
6008110 | 616,33 |
6008310 | 945 |
6008310 | 1080 |
6001110 | 1530 |
6008110 | 2663,67 |
6001110 | -62 |
6001110 | -50,66 |
6001110 | 40 |
6001110 | 50,66 |
6001110 | 50,66 |
6001110 | 62 |
6001310 | 135 |
5001310 | -550 |
6008110 | -120 |
5001110 | -6 |
5001110 | -1466 |
5001310 | -64 |
8001310 | -63,72 |
And another table called GL_COSTS
GL Account no.
6001110 |
6001152 |
6001310 |
How can I sum values only for those GL Account no. which are in GL_COSTS table?
Thanks in advance!
Solved! Go to Solution.
Hi samto22,
Have you built a relationship between the two tables? Click "manage relationship" and set many-to-one relationship as below:
Then you can create a measure using both of DAX formula like below:
Measure = CALCULATE ( SUM ( Data[Amount] ), FILTER(DATA, DATA[GL Account no.] = RELATED(GL_COSTS[GL Account no.]) )) Measure = CALCULATE ( SUM ( Data[Amount] ), GL_COSTS )
Regards,
Jimmy Tao
@Anonymous
If the two tables are related by Account No. you can use
Measure = CALCULATE ( SUM ( Data[Amount] ), GL_COSTS )
if the tables are not related...you can try
Measure 2 = CALCULATE ( SUM ( Data[Amount] ), FILTER ( Data, Data[GL Account no.] IN VALUES ( GL_COSTS[GL Account No.] ) ) )
These two tables are related by Account No. so I used your first formula but unfortunately it does not give any result 😞
I created this measure in DATA table, is it ok?
Hi samto22,
Have you built a relationship between the two tables? Click "manage relationship" and set many-to-one relationship as below:
Then you can create a measure using both of DAX formula like below:
Measure = CALCULATE ( SUM ( Data[Amount] ), FILTER(DATA, DATA[GL Account no.] = RELATED(GL_COSTS[GL Account no.]) )) Measure = CALCULATE ( SUM ( Data[Amount] ), GL_COSTS )
Regards,
Jimmy Tao
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |