Forum Discussion

kolson256's avatar
kolson256
New Member
9 years ago
Solved

Dynamic Column Calculation Based on Multiple Slicers

I have a table of sales opportunities, and I want to create a column which displays the ratio of the opportunity's value compared to all active opportunities. I was able to accomplish this with the f...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi kolson256,

     

    According to your description, using ALLSELECTED Function (DAX) should meet your needs. The formula below is for your reference.:smileyhappy:

    RATIO = SUM ( Table1[Amount] ) / CALCULATE ( SUM ( Table1[Amount] ), ALLSELECTED () )

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

Hi kolson256,

 

The value of a Calculated Column is computed during data refresh and uses the current row as a context; it does not depend on user interaction in the report.

 

In this scenario, you should create a Measure instead, then show it with the Opportunities on the report. For more details about differences between Calculated Columns and Measures, please refer to this article.

 

And the formula below to create the measure is for your reference.:smileyhappy:

RATIO =
SUM ( Data[Amount] )
    / CALCULATE (
        SUM ( Data[Amount] ),
        ALLEXCEPT (
            Data,
            Data[Quarter],
            Data[Territory],
            Data[Product Family],
            Data[Opportunity Stage]
        )
    )

 

Regards

seena's avatar
seena
Frequent Visitor
9 years ago

Is it possible to add additional filter to this formula which can filter the above data for TOPN customers also. I want it done dynamically.Cannot use the visual filter.