Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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?

MFelix
Super User
Super User

Hi @Anonymous ,

 

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

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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.

Hi @Anonymous ,

 

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 Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable


@MFelix wrote:

Hi @Anonymous ,

 

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?

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

Regards
MFrlix

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable


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

Hi @Anonymous ,

 

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 Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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

 

Package "P" SelectedPackage "P" SelectedNo FilterNo FilterPackage "B" SelectedPackage "B" SelectedSample DataSample Data

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.