Forum Discussion

unnijoy's avatar
unnijoy
Icon for Post Prodigy rankPost Prodigy
3 years ago
Solved

Fix Denominator

I have the month on  month sales data . Month , City , Country , Region , Sales , Month Flag. These are the column headers. I have a measure that will assign Month flag number from 0 -12. Now i need to get a measure wich will give me the percetage of sales compare to the total sales on the month which is flages as 12. 

I need the Numerator as total sales of the Month in which month flag is comomg as 12. I create a measure as follows.

 

## Sales %=
 
VAR Month_Check = SELECTEDVALUE('# Sales'[Month Flag])
 
VAR _Numerator = CALCULATE(    SUM(  # Sales[Sales]  ),    FILTER(  # Sales,    # Sales[Month Flag] = Month_Check  )  )
 
VAR _Denominator = CALCULATE(    SUM(  # Sales[Sales]  ),    FILTER(    ALL(  # Sales ),   # Sales[Month Flag=12))
 
 
 VAR _Diff = _Numerator - _Denominator

RETURN
DIVIDE(_Diff , _Denominator)

 

Here I am facing as Issue. If i select any region then insted of taking the total sales of that Region ( whose Month Flag is 12), it is taking the oveall sales for the month whose month flag is coing as 12.

 

Lets say Month flag 12 is coming for FEB 2022. and on the graph i have months from FEB 2022  TO JAN 2023.
FEB 2022 total Sales = 250 and total sales for JAN 2023 = 210. When am not using any filter it will show correctly. But if i select any Region , insted of taking the total sales for that Region on FEB 2022 it is still taking Oveall Sales for FEB 2022. 

How can we fix this.

Need Help
  • unnijoy's avatar
    unnijoy
    3 years ago

    Hi All,

    I find a solution for this. Below is the solution

    VAR _Denominator = CALCULATE(    SUM(  # Sales[Sales]  ),    FILTER(    ALL(  # Sales{Month] ),   # Sales[Month Flag=12)).

     

    Hope this may hep for others who are facing the same issiue.

4 Replies

  • jaweher899's avatar
    jaweher899
    Icon for Impactful Individual rankImpactful Individual
    3 years ago

     

    To make sure that the measure only calculates the total sales of the month whose month flag is 12 in the selected region, you need to modify the calculation for the denominator. Instead of using the ALL function, use the CALCULATE function with the SUM of sales for the region whose month flag is 12. Here's how you can modify the measure:

    ## Sales % =

    VAR Month_Check = SELECTEDVALUE('# Sales'[Month Flag])
    VAR _Numerator = CALCULATE( SUM( # Sales[Sales] ), FILTER( # Sales, # Sales[Month Flag] = Month_Check ) )
    VAR _Denominator = SUMX( FILTER( # Sales, # Sales[Month Flag] = 12 ), CALCULATE( SUM( # Sales[Sales] ), # Sales[Region] = MIN(# Sales[Region]) ) )
    VAR _Diff = _Numerator - _Denominator

    RETURN
    DIVIDE(_Diff , _Denominator)

    • unnijoy's avatar
      unnijoy
      Icon for Post Prodigy rankPost Prodigy
      3 years ago

      hi jaweher899 ,

       

      Thanks for the quick help. In the measure i can see that the focus is on Region. Actualy we have oter filter too. say Country,City and Region. and the month number is from 0-13. In that we are putting the filter ifor denominator as Month Flag is 12. So at the end what ever we are filter on Say if i select Country as India and city as Mumbai. Then It should take the denominator as follow.

      City - Mumbai.
      Country - India
      Region - Asia. 

      So at the end wat ever the total sale by wat ever filter we put.

      How can we midify it.

      Thanks again for your help. 

  • unnijoy's avatar
    unnijoy
    Icon for Post Prodigy rankPost Prodigy
    3 years ago

    Hi All,

    I find a solution for this. Below is the solution

    VAR _Denominator = CALCULATE(    SUM(  # Sales[Sales]  ),    FILTER(    ALL(  # Sales{Month] ),   # Sales[Month Flag=12)).

     

    Hope this may hep for others who are facing the same issiue.