Forum Discussion

fridge81's avatar
fridge81
New Member
8 years ago

Dynamic Column calculation based on Slicer Selection

HI,

 

I'm relatively new to PowerBI and have run into some trouble calculating a percentage in a way that the calculation updates depending on what filters are applied.  I have a scaled down example of what I'm trying to accomplish attached below.  My objective is to show market share per office, where market share is the sum of all revenues divided by the sum of all market sizes.  However, I want the calculation to update dynamically depending on what filters are applied in slicers to Industry Classification and Project Size.  Here is my raw data:

 

 

 

Sample DataSample Output with no filtersSample Output with filters applied to Project Size (Medium) and Industry Classification (1 OR 3)

Need Help

5 Replies

    • fridge81's avatar
      fridge81
      New Member
      8 years ago

      I've tried a number of solutions using ALLSELECTED (and various combinations of pivoted and unpivoted columns), and nothins seems to be working - I suspect my syntax is off.  In my most recent attempt, I was able to summarize all of the market size columns into a single column so that my input data now looks like the Sample Data below.  I've tried a few approaches, including calculating a column or set of columns to get my desired result, but nothing quite works.  My most recent approach was to try and create a table based on summarizing the existing one using

       

      Table = SUMMARIZE(Sheet1,Sheet1[Office],"Filtered Revenue",CALCULATE(SUM(Sheet1[Revenue]),ALLSELECTED(Sheet1[Industry Classification]),ALLSELECTED(Sheet1[Project Size])),"Filtered Market Size",CALCULATE(sum(Sheet1[Attributable Market Size]),ALLSELECTED(Sheet1[Industry Classification]),ALLSELECTED(Sheet1[Project Size])))

       

      This syntax yields the output pictured below, regardless of what slicers are applied.  I've used similar expressions (with adjustments to suit the calculation result, e.g. column vs measure vs table) to try and calculate columns and measures in my existing table and gotten similar results.  Can anyone point me in the direction of where I'm going wrong?

       

      Sample DataResult of DAX code

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        8 years ago

        Hi fridge81,

         

        I am confused,  Why should this simple formula not work

         

        =SUM(sheet1[Revenue])/SUM([sheet1[Attributable Market Size])

         

        Format this as a % age.  Now when you filter/slice your data, the figures should change.