Interactive Sum and Variation by Filter

[corporate]

Hi everyone,

I import this table in Powe BI:

I create several filters on the page: Month, Target, Region... and I want that, when the user select a Month from the filter, Power BI calculates the sum of Contactability per Month and the difference of these sums between the selected Month and all other Months, and I can show these difference on an histogram.

For example, if the user select Month '201901' I want the histogram to show:

And, of course, the histogram should react to other filters (Target, Region...).

Does anyone know how I can achieve this?

Thank you very much!

[Icey · Accepted solution]

Hi @corporate ,

Please check:

1. Create a table.

Month = VALUES('Table'[Month])

 

2. Create a measure.

Difference = 
VAR SelectedMonth =
    SELECTEDVALUE ( 'Table'[Month] )
VAR ThisMonth =
    MAX ( 'Month'[Month] )
VAR SelectedMonthValue =
    CALCULATE ( SUM ( 'Table'[Contactability] ), 'Month'[Month] = SelectedMonth )
VAR ThisMonthValue =
    CALCULATE ( SUM ( 'Table'[Contactability] ), 'Table'[Month] = ThisMonth )
RETURN
    ThisMonthValue - SelectedMonthValue

3. Create a Clustered column chart and all slicers from 'Table'.

4. Test.

For more details, please check the attached PBIX file.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[corporate]

Thank you very much @Icey !

It works and solved my problem.

Thank you, again.

Valentina

[nandukrishnavs]

@corporate  - can you provide a sample dataset/pbix file?

[corporate]

@nandukrishnavs unfortunately I can't upload a pbix file and my company doesn't allow me to share a Dropbox or Drive link.

I post here a dataset sample:

Month	Target	Region	Contactability
201911	Residential	Lombardia	5
201911	Residential	Lazio	1
201911	Micro Business	Lombardia	1
201912	Micro Business	Piemonte	6
201912	Residential	Lazio	4
201912	Residential	Lombardia	3
201912	Micro Business	Piemonte	1
201912	Micro Business	Lazio	2
202001	Micro Business	Lombardia	3
202001	Micro Business	Veneto	5
202001	Micro Business	Piemonte	4
202001	Residential	Lombardia	2
202002	Residential	Lazio	1
202002	Residential	Piemonte	1
202002	Residential	Veneto	3
202002	Residential	Lombardia	4
202002	Micro Business	Lazio	4
202002	Micro Business	Piemonte	2

and here is how the dashboard should look like when I select Month = '201912' and Target = 'Residential':

The histogram is built with manually inserted data, because my problem is that I don't know how to automatically build it using the data in the above table. 

Thank you very much!