Calculating proportion of filtered total for each row

[Jolly]

Hello,

I have a dashboard page that contains 3 different slicers for a set of data. I want to be able to add a calculated column to the data that shows, for each row, the proportion of the total (of a numeric column) after the slicers on the dashbord have been applied. I also want to use this proportion for other calculated columns that will also need to respond to the slicers.

Previous searching suggest that the following should work (I am trying to calculate, for each row, the proportion of the filtered students):

Proportion of selected students = Table1[No. students]  /  CALCULATE( SUM ( Table1[No. students] ),ALLSELECTED(Table1))

However, this does not appear to respond to dashboard slicers. The proportions displayed are always the proportions of the unfiltered total number of students. 

Any help is very much appreciated.

[vanessafvg]

@Jolly  

difficult to understand what you are trying to do here

can you provide a screen shot of your filters?  

i am assuming below is your measure?

so you want to know the figure you have filtered against the total?

ie.  

Proportion of selected students =
DIVIDE (
    CALCULATE ( SUM ( Table1[No. students] ), ALL ( table1 ) ),
    CALCULATE ( SUM ( Table1[No. students] ) )
)

[Jolly]

@vanessafvg

Hi, at the moment the dashboard only contains a table (though I'd like to build graphs off these results):

Here the sum of proportions column is calculated using:

Proportion of selected students = Table1[No. students]  /  CALCULATE( SUM ( Table1[No. students] ),ALLSELECTED(Table1))

When I use the filters however, the proportions remain as they were in the unfiltered table - I need these to be as a propotions of the new filtered total: (in this example they should all be around 0.33).

Thank you for your help!

[v-caliao-msft · Accepted solution]

@Jolly,

Please create a measure instead of column.

Regards,

Charlie Liao

[vanessafvg]

@Jolly  using your logic i dont quite understand why you getting the result you are

i test it and using this measure it worked correctly - the only think i did difference was how i wrote the divide statement.

proportion = DIVIDE( COUNTROWS(BU), CALCULATE(COUNTROWS(BU), ALLSELECTED(bu)))