Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
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?
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsDisregard 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
Proud to be a Super User!
Check out my blog: Power BI em Português
@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?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
@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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHere's what I'm currently seeing in Power BI: