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 Guys,
I am a little stuck here in the calculation.
For e.g. I have the below data:
Ward name | Temp - Borough | Cost |
Barking and Dagenham - Longbridge | Barking and Dagenham | £100.00 |
Barking and Dagenham - Mayesbrook | Barking and Dagenham | £100.00 |
Barking and Dagenham - Parsloes | Barking and Dagenham | £100.00 |
Barking and Dagenham - River | Barking and Dagenham | £100.00 |
Barking and Dagenham - Thames | Barking and Dagenham | £100.00 |
Barking and Dagenham - Valence | Barking and Dagenham | £100.00 |
Barking and Dagenham - Village | Barking and Dagenham | £100.00 |
Barking and Dagenham - Whalebone | Barking and Dagenham | £100.00 |
Barnet - Brunswick Park | Barnet | £100.00 |
Barnet - Burnt Oak | Barnet | £100.00 |
Barnet - Childs Hill | Barnet | £100.00 |
Barnet - Colindale | Barnet | £100.00 |
Barnet - Coppetts | Barnet | £100.00 |
Barnet - East Barnet | Barnet | £100.00 |
Barnet - East Finchley | Barnet | £100.00 |
Barnet - Edgware | Barnet | £100.00 |
What I would like to have is If i select any value in the Ward name filter it should show me the total cost for Temp - Borough pertaining to that Ward. E.g. If I select Ward name: Barking and Dagenham - Longbridge; I should get the total cost of Barking and Dagenham (Temp - Borough) which is 800. Same thing should happen if I select any filter Ward name: Barnet - Brunswick Park falls under Barnet temp borough for which the cost should be 800
Also, what I would want to have is show the comparison in a graph; for e.g. E.g. If I select Ward name: Barking and Dagenham - Longbridge; a Bar graph will only showcase all the wards available in the Barking and Dagenham (Temp - Borough) . I already have mapped the tables for the same & tried to Edit interations by disabling the filters and highlights. But its not working.
Any assistance on this is highly appreciated as I don't know how to proceed.
Regards,
Prathamesh Sable
Solved! Go to Solution.
Hi @Anonymous
First part is easy, please see the measure below.
Measure = CALCULATE( SUM( 'YourTable'[Cost] ), ALL( 'YourTable' ), VALUES( 'YourTable'[Temp - Borough] ) )
For the second part you probobly need to duplicate your table and join on Temp - Borough
Hi @Mariusz ,
Thanks for the measure. It helps.
Can you explain a little more on the second part on how to join it because I tried the join its failing. Also, there are 64 more calculations like cost that will be dependent.
Regards,
Prathamesh Sable
Hi @Anonymous
Please see the below outcome.
If the above is you desired outcome then all you need to do is:
Use Query editor to "Reference" original table, remove all columns but ( "Temp - Borough" and "Ward Name" ), next join the two tables like on the screenshot below and use "YourTable - Copy" to filter.
Let me know if you need any help!
Hi @Mariusz ,
I have already tried this but then the calculation remains constant across all the other selections in the graph
The relationship becomes many to many as well.
Suggestions to fix this?
Regards,
Prathamesh Sable
Since you are wanting to see summary information AND a visual based on a selection of a ward I would suggest a slicer with the ward names.
1. Go to "Edit Queries"
2. Select your Ward Name column and then click "Split Column" in the "Transform" tab
3. Split Ward Name by " - " (no quotes) so that the first and second half of each name are seperate
4. Close and Apply
5. Create a bar graph with Ward Name.1 as Legend and Ward Name.2 as Axis and Cost as Values
6. Create a slicer based on Ward Name.1
7. Create a card with Cost and choose a Sum calculation
Let me know if you have any questions
Hi @ntaylo06
Thanks for the transform option. With Ward name 1; itdefinitely will work. I can even use a separate mapping table to make work.
However, I need to select Ward name 2 as the Slicer instead of Ward Name 1& the graph should showcase All the wards in Ward 2. This is where it will become tricky.
Any suggestions?
Regards,
Prathamesh Sable
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |