Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
lo,
I'm quite new to Power BI so apologies if this is a silly question but I cannot find the solution to something I thought would be easy in the same way I would do it in Excel.
In Report view, I have a column which uses DAX to Sum it's value (from thousand of rows). I have another column which pulls through a static value from another table. Both are displayed in Report View based on SiteID.
I would like to do a further calculation whereby I want to display the value of Column A minus (-) column B = column C.
I can't get this to work at table level as I end up with duplicated rows showing in Report View, i think because final values are currently being automatically summed in the Report View.
Can I do this at Report level please?
Solved! Go to Solution.
@AP23 Refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another:
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8
Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-soluti...
If you find this insightful, please provide a Kudo and accept this as a solution.
Just to add, if I use TableC[SiteID] on the left side of the = operator, it then doesn't show TableA[SiteID] on the right side of the = operator.
@AP23 Refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another:
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8
Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-soluti...
If you find this insightful, please provide a Kudo and accept this as a solution.
@AP23 I'm unable to get your last point but let's try to create a simple measure from these two columns just like:
measure = sum('table'[column A]) - sum('table'[column B])
Or else if it doesn't work, can you provide me the sample data for the same.
If you find this insightful, please provide a Kudo and accept this as a solution.
Many thanks for your help.
Having looked at this further, I think the issue is to do with my table relationships.
I have the following structure:
Table A <->1:1 Table B ->1:many Table C
10 rows 10 rows 1,000 rows
I want to add a column in Table A whereby it sums the multiple values of a column in Table C.
However, the formula I use doesn't give me the option to filter based on column "Site ID" .
I want to use: CALCULATE(SUM(TableC[column1]),TableA[SiteID]=TableC[SiteID]))
However, I it doesn't give me the option to locate TableC[SiteID] when used on the right side of the = operator. It does, however, show it if I use it on the left hand side of the = operator.
I have tried changing my table relationships so that it becomes the following but it makes no difference.
Table A <-1:many Table B ->1:many Table C
10 rows 10 rows 1,000 rows
Is there a command I can use to pull in the TableC[SiteID] value in my CALCULATE equation? I've tried RELATED but this hasn't worked.
Any help much appreciated! thank you.
User | Count |
---|---|
98 | |
90 | |
78 | |
72 | |
65 |
User | Count |
---|---|
114 | |
97 | |
95 | |
68 | |
65 |