Power BI - Selecting and Filtering Columns based on Slicer Selection

0
votes

Hello Stack Overflow Community, In the attached excel sheet image, there are 5 columns (cols B to F) that have arbitrary sales numbers for years 2016-2020. In the report there will be a year slicer of each of these years. I want to calculate the total sales depending on the selection in the year slicer. However, there are 15 columns towards the end which dictate whether a particular company should be included in the total sales calculation. Only if the column corresponding to the selection by the user is N, the value should be included in the total sales calculation.

Example: If the user selects 2016,2017 and 2018 in the year slicer, Power BI should look into the column Excluded 2016-2017-2018 and include/ exclude the sales numbers of the respective years and finally return a table as below

Year Sales (Total) 2016 393 2017 561 2018 580

enter image description here

1
powerbidax

1 Answers

0
votes

There is two problem in your case : the first is to be able to calcule with the filter and I think it will be easier if you do this in Excel and the second problem is to use a filter on a combinaison of imput to select only one value.

For the fist problem you could probably do something like that : enter image description here

=IF(H2="Y";F2;0) / =IF(I2="Y";G2;0) / =IF(J2="Y";G2+F2;0) and use the power of excel to slide the formula to the bottom of your table

enter image description here

And the next step is to sum the entire column and pivot the table :

enter image description here

But for the selection of the right row with your slicer on date i can't find a solution maybe you need to build a slider for all combinaison : 2016 / 2017 and 2016-2017,...