Forum Discussion

imranamikhan's avatar
imranamikhan
Icon for Helper V rankHelper V
5 years ago

Ignore multiple filters in table

Hi everyone,

 

I have a fairly unique requirement I am trying to solve.

 

  • My data sources are 12 Excel tabs. Each Excel tab is numbered between 1-12. The numbers represent the month of the year the tab was created (1 = January, 2 = February etc).

I have appended these tabs in Power Query and the result is the following structure:



I unpivoted the Budget & Period columns to return the following structure:

 

I have created a table visual and added multiple slicers to that table.

 

  1. Filtered by Project B
  2. Filtered out the Budget attribute
  3. Filtered by File Number 3

 

The result is a table which displays the values for the Period attributes.

 


Below is the same logic applied to the actual PowerBI visual using real data:

 

Requirement/problem:

 

I need to add an additional measure as a column displaying the value for the Budget attribute. However, the values for Budget Attribute should return Excel File 1 value for Period 1, Excel File Number 2 value for Period 2, Excel File Number 3 value for Period 3 and so on. I initially tried building a custom table with each Period and then creating specific measures, but this resulted in issues with my calculations such as the running totals.

 


Any thoughts on how to address this problem?

 

best regards,

AmiK

Need Help

4 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    5 years ago

    imranamikhan Sorry, I am getting confused because you are using Period for the Excel sheet/tab and also as an additional attribute. When you say Period 1 should return Excel file 1 budget, what do you mean by that? It looks like Project A is in Sheet 1 with Period 1, Period 2, etc. Did you want a different budget for each of those periods? What dimensions should it keep? 

  • imranamikhan's avatar
    imranamikhan
    Icon for Helper V rankHelper V
    5 years ago

    Thanks for responding Allison. Agree it is confusing to explain. I have sent you the PBIX file and the equivalent Excel version I am trying to replicate.

     

    For better context: every month we take a snapshot of current year financial forecasts and save them as separate tabs. These “snapshots” contain forecast data for each month/period of the year as separate columns (Period 1, Period 2, Period 3 etc).

    Each snapshot also includes a single Budget column.

    My data model appends all of the snapshot data into one table and then unpivots them. There is a column which identifies which snapshot each row is sourced from (e.g. snapshot 1, 2, 3).

    A user can use a slicer to filter by snapshot (e.g. snapshot 12).

    In my unpivoted table, for each period/attribute row (Period 1, Period 2, Period 3 etc), I want to create a Budget column which returns the Budget column from the equivalent snapshot number. So for Period 1 row, return the Budget column in snapshot 1, for Period 2 row, return the Budget column in snapshot 2 etc.

     


    I have attempted the below measure to ignore the period slicer but got stuck. Essentially the measure is trying to use the Sort Column as a helper column to help the measure identify which Sheet Number to reference the Budget column from.

     

    Current Authority = CALCULATE(

    SUM('Programme Financials Unpivot'[Value]),

    FILTER(

                ALL('Programme Financials Unpivot'),

                'Programme Financials Unpivot'[Sheet Number (Period)] = 'Programme Financials Unpivot'[Sort Column] && 'Programme Financials Unpivot'[Project No.] = [Selected Project]  && 'Programme Financials Unpivot'[Attribute] = "Current Authority"

                ))

     

     

    I hope this makes sense?

    • v-janeyg-msft's avatar
      v-janeyg-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi, imranamikhan 

       

      I am a little puzzled. Can you explain more clearly?

      Your needs are simple, but I can’t understand this, so I can’t proceed to the next step.

       

      Best Regards

      Janey Guo