Forum Discussion

corporate's avatar
corporate
Helper I
6 years ago
Solved

Interactive Sum and Variation by Filter

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!

Need Help
  • Icey's avatar
    Icey
    6 years ago

    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.

4 Replies

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    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's avatar
      corporate
      Helper I
      6 years ago

      Thank you very much Icey !

       

      It works and solved my problem.

       

      Thank you, again.

       

      Valentina

    • corporate's avatar
      corporate
      Helper I
      6 years ago

      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:

      MonthTargetRegion Contactability
      201911ResidentialLombardia5
      201911ResidentialLazio1
      201911Micro BusinessLombardia1
      201912Micro BusinessPiemonte6
      201912ResidentialLazio4
      201912ResidentialLombardia3
      201912Micro BusinessPiemonte1
      201912Micro BusinessLazio2
      202001Micro BusinessLombardia3
      202001Micro BusinessVeneto5
      202001Micro BusinessPiemonte4
      202001ResidentialLombardia2
      202002ResidentialLazio1
      202002ResidentialPiemonte1
      202002ResidentialVeneto3
      202002ResidentialLombardia4
      202002Micro BusinessLazio4
      202002Micro BusinessPiemonte2

       

      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!