cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DataVis369 Regular Visitor
Regular Visitor

Displaying Sales Ratio with Slicer

I'm trying to calculate the percentage of sales that are included in a given package. I have a slicer on these packages and I would like to display our agent's percent of sales in the selected package on a scatter plot.

 

To do this, I would like to create an expression where the total sales from a given package are divided by the total sales across all packages. Since the SUM expression naturally applies all filters, I'm focusing more on how to disregard the package slicer in the denominator. Based off what I've read online, the following expression should work:

 

Total Sales Selected = calculate(sum(Query1[TOTAL_SALES]), ALL(Query1[C_PACKAGED_DISC]))

However I'm still coming up with innacurate numbers that change when I select different packages in the slicer. Because the total sales across all packages is a constant, the denominator, therefore, should remain the same regardless of the package I slice by.

 

Can anyone help me out here?

9 REPLIES 9
Super User III
Super User III

Re: Displaying Sales Ratio with Slicer

Hi @DataVis369 ,

 

Based on the context you have this should give the correct value, can you share a sample of your data?

 

Regards,

MFelix


Regards

Miguel Felix


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

Proud to be a Datanaut!




DataVis369 Regular Visitor
Regular Visitor

Re: Displaying Sales Ratio with Slicer

Here's what I'm currently seeing in Power BI:

 

Picture B filter.pngPackage "P" SelectedPicture no filter.pngNo FilterPicture P filter.pngPackage "B" SelectedData.pngSample Data

DataVis369 Regular Visitor
Regular Visitor

Re: Displaying Sales Ratio with Slicer

Disregard the labels but basically I need "Selected Package Total Premium" to remain at 426,051.39 regardless of what I selected in the slicer. All I'm trying to do is simply add up the total dollar amount across all packages and have it stay that way.

Super User III
Super User III

Re: Displaying Sales Ratio with Slicer

Hi @DataVis369 ,

 

Do you want to have in the column always the same value? Try the following code:

 

Total Sales Selected =
SUMX (
    SUMMARIZE (
        ALL ( query1[C_PROD]; query1[Country_state] );
        query1[C_PROD];
        "Premium"; SUM ( query1[TOTAL_PREM] )
    );
    [Premium]
)

Regards,

MFelix


Regards

Miguel Felix


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

Proud to be a Datanaut!




DataVis369 Regular Visitor
Regular Visitor

Re: Displaying Sales Ratio with Slicer

Interestingly, the following code works correctly to remove ALL filters. However I'm only trying to remove the filter on the specific column that my slicer is tied to (C_PROP_INSD), while retaining additional slicers on other columns.

 

Selected Package Total Premium = CALCULATE( SUM(Query1[TOTAL_PREM]), ALL(Query1))

Is there a reason why trying to specify a column would throw everything off?

 

Also are there any other methods of doing this? Could I define a variable and then tie that variable to whatever package is selected in my slicer? That way I could just sum the variable. Additionally is it possble some of this logic would function correctly with regular filters instead of utilizing slicers?

DataVis369 Regular Visitor
Regular Visitor

Re: Displaying Sales Ratio with Slicer


@MFelix wrote:

Hi @DataVis369 ,

 

Do you want to have in the column always the same value? Try the following code:

 

Total Sales Selected =
SUMX (
    SUMMARIZE (
        ALL ( query1[C_PROD]; query1[Country_state] );
        query1[C_PROD];
        "Premium"; SUM ( query1[TOTAL_PREM] )
    );
    [Premium]
)

Regards,

MFelix


 

What are you referring to with the columns C_PROD and Country_State?

Super User III
Super User III

Re: Displaying Sales Ratio with Slicer

CPROD is your CPRODISND and country is the column it appears on your table visual.

Regards
MFrlix

Regards

Miguel Felix


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

Proud to be a Datanaut!




DataVis369 Regular Visitor
Regular Visitor

Re: Displaying Sales Ratio with Slicer


@MFelix wrote:
CPROD is your CPRODISND and country is the column it appears on your table visual.

Regards
MFrlix

Sorry you can disregard the column headers in that picture. That table is just used to verify data.

 

What I need is simply the percentage of sales in a package divided by the total sales.

Super User III
Super User III

Re: Displaying Sales Ratio with Slicer

Hi @DataVis369 ,

 

Try this for total sales value:

 

Total Sales Selected =
SUMX (
    SUMMARIZE (
        ALL ( query1[C_PROD] );
        query1[C_PROD];
        "Premium"; SUM ( query1[TOTAL_PREM] )
    );
    [Premium]
)

 

CPROD is your program column.

 

Regards,

MFelix


Regards

Miguel Felix


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

Proud to be a Datanaut!




Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors