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 ...
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.

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!