Show calculation for entire group by selecting a single filter option

[Anonymous]

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

[Mariusz]

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

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

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

[Mariusz]

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!

Best Regards,
Mariusz

Please feel free to connect with me.

[Anonymous]

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

[Anonymous · Accepted solution]

I was able to fix it. The solutions helped.

Just used the Ward name from Mapping table. I didn't realize it.

Thanks a lot @Mariusz  & @ntaylo06  for your suggestions and help.

Regards,

Prathamesh Sable

[ntaylo06]

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

[Anonymous]

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