cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kolson256 Frequent Visitor
Frequent Visitor

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 following column:

 

RATIO = Data[Amount] / CALCULATE(SUM(Data[Amount]),ALL(Data))

 

But my next step is to have this ratio be dependent on four slicers I have added: Quarter, Territory, Product Family, Opportunity Stage. When a user selects the 1st Quarter, EMEA Territory, ABC Product Family, Validated Opportunity Status, I want this column to adjust the denominator of the above calucation to only include opportunities which meet these criteria.

 

Can anyone shed any light on how I could do this? I am new to Power BI, and the filtering functions have not been very intuitive to me. Thank you for any help you can provide.

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: 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.Smiley Happy

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

r1.PNGr2.PNG

 

Regards

View solution in original post

8 REPLIES 8
Super User
Super User

Re: Dynamic Column Calculation Based on Multiple Slicers

You should be able to achieve this by using ALLEXCEPT function

 

here is more information https://msdn.microsoft.com/en-us/library/ee634795.aspx

 

Let me know if need further help with the formula. Thanks!

 






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





PavelR Established Member
Established Member

Re: Dynamic Column Calculation Based on Multiple Slicers

Hi @kolson256,

 

as @parry2k correctly said, ALLEXCEPT function will do what you want.

 

Just use modified formula:

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

 

Regards.

Pavel

Baskar Super Contributor
Super Contributor

Re: Dynamic Column Calculation Based on Multiple Slicers

In this case ALLEXCEPT will help u.

 

u have to understand the difference between ALL, ALLSELECTED, ALLEXCEPT.

 

your calculation is almost correct dude, have one simple correction .

 

u have to add ALLEXCEPT and apply what every column u want.

 

thats it.

 

cool

v-ljerr-msft Super Contributor
Super Contributor

Re: Dynamic Column Calculation Based on Multiple Slicers

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.Smiley Happy

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

 

Regards

kolson256 Frequent Visitor
Frequent Visitor

Re: Dynamic Column Calculation Based on Multiple Slicers

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

QuarterTerritoryFamilyStageAmountRatio
1EMEAABCIdentify10010%
1EMEAABCIdentify50050%
1EMEAABCClosed / Won40040%
Total   1000100%

 

Table 2

QuarterTerritoryFamilyStageAmountRatio
1EMEAABCIdentify10017%
1EMEAABCIdentify50083%
Total   600100%
v-ljerr-msft Super Contributor
Super Contributor

Re: 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.Smiley Happy

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

r1.PNGr2.PNG

 

Regards

View solution in original post

kolson256 Frequent Visitor
Frequent Visitor

Re: Dynamic Column Calculation Based on Multiple Slicers

@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.

Highlighted
seena Frequent Visitor
Frequent Visitor

Re: Dynamic Column Calculation Based on Multiple Slicers

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.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 138 members 1,806 guests
Please welcome our newest community members: