Forum Discussion
Dynamic Column Calculation Based on Multiple Slicers
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
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
Thank you everyone, using ALLEXCEPT does seem to have put me on the right track.
v-ljerr-msft, the problem I still run into using your formula is it always calculates the ratio for each opportunity based on other opportunities which share all 4 attributes (Quarter, Territory, Product Family, Opportunity Stage). What I would like to have in the denominator is every opportunity which matches the user's current selections in the slicers.
For instance, if the user has selected 1st Quarter, EMEA Territory, ABC Family, then my table will show all opportunities of all stages. I would like the ratio to then show the Amount / SUM(All Opportunities currently displayed in the table). For instance I would like the table to look like Table 1 if I haven't made a selection in the Opportunity Stage slicer, but look like Table 2 if I have selected the Identify Stage.
Table 1
| Quarter | Territory | Family | Stage | Amount | Ratio |
| 1 | EMEA | ABC | Identify | 100 | 10% |
| 1 | EMEA | ABC | Identify | 500 | 50% |
| 1 | EMEA | ABC | Closed / Won | 400 | 40% |
| Total | 1000 | 100% |
Table 2
| Quarter | Territory | Family | Stage | Amount | Ratio |
| 1 | EMEA | ABC | Identify | 100 | 17% |
| 1 | EMEA | ABC | Identify | 500 | 83% |
| Total | 600 | 100% |
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 Thank you, that did indeed do the trick. I had gotten it to work with ALLSELECTED shortly after my last post by taking the advice from Baskar to read into ALL, ALLEXCEPT, and ALLSELECTED, and both of your comments were very helpful. I have accepted your most recent answer as it did fully resolve the issue.
Thanks again.