Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Using Calculation with Slicer to use in Measure Calculation

I have the below table displayed in a report:

 

StateDiscounted SalesAll Sales
MA50200
SC100500
NY150300

 

I want the below [Discounted % of Total] column to be created below:

 

StateDiscounted SalesAll SalesDiscounted % of Total 
MA502005%
SC10050010%
NY15030015%
Total300100030%

 

The issue is everytime I create a new measure and try to calculate the All Sales Total (1000) it automatically groups it by ['State'], so using first row as an example, it displays 25% (50/200) intead of 5% (50/1000). I've tried CALCULATE, SWITCH, FILTER etc and all with the same result. 

 

Also in my report I have a date slicer by year, quarter and month which pulls from another [Date] Column, so when I apply the slicer by Date, I want table to update. So for example, in the first row, if I filtered by 2021, and the total number of sales was 500 instead of 1000, [Discounted % of Total] would update to 10% (50/500)

 

I've been able to find the total sales figure by using the MAX aggregation on [All Sales] bu then the number doensn't respond to the slicer. 

 

Thanks in advance!

 

 

 

 

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    4 years ago

    Anonymous 

    pls try this

    % of total = sum('Table'[Discounted Sales])/CALCULATE(sum('Table'[All Sales]),all('Table'))

    pls see the attachment below

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    4 years ago
    Discounted % of total =
VAR currentValue =
    CALCULATE ( SUM ( 'Table'[Discounted Sales] ) )
VAR totalValue =
    CALCULATE ( SUM ( 'Table'[Discounted Sales] ), ALLSELECTED ( 'Table' ) )
RETURN
    DIVIDE ( currentValue, totalValue )
  • ryan_mayu's avatar
    ryan_mayu
    Super User
    4 years ago

    Anonymous 

    pls try this

    % of total = sum('Table'[Discounted Sales])/CALCULATE(sum('Table'[All Sales]),all('Table'))

    pls see the attachment below