Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Calculating a measure based on conditional result from another measure

Hello,

 

I'm currently trying to set up a measure which works as follows.

 

There is a measure which calculates the absolute change in the share within a market. So let's say you have country UK, and the company had ownership in terms of share [last year] of 30% and now [this year] has become 31%, this would be a share change of 100 bps. This measure is calculated by checking a company's say sales in this region as a percentage of total sales in (a given category). So using this measure I have created a table which shows the countries, and the 3 measures which show current share, previous year share and the share change.

 

Some background info on the setup(using dummy tables and measures with the right logic)

 

Last Year Sales = Calculate(SUM(Facts[Sales EURO]), Calendar[Year] = "LY")
This Year Sales = Calculate(SUM(Facts[Sales EURO]), Calendar[Year] = "TY")

Market Share LY = DIVIDE([Last Year Sales], CALCULATE(Last Year Sales], ALL(Products[Company])))
Market Share TY = DIVIDE([This Year Sales], CALCULATE(This Year Sales], ALL(Products[Company])))
Market Share Growth = [Market Share TY] - [Market Share LY]

Facts Table contains sales in euros,dollars,weight(kg)
Products contains info on the company, brand etc
Calendar contains info on the year Type
There is a Market table which contains info on the countries e.g. Market[Country]

 

 

Now what I want to do is calculate the total sales value contributed by the countries which have had had a positive absolute share change from the previous year compared to this year. So let's say out of 10 countries only france and spain had positive gain in share, i want to calculate the respective total sales only for these two countries with a measure. say total sales was 10M, but only france (1M sales) and spain (0.5M sales) had a gain in share from LY to TY, i want the measure to return 1.5M

 

Hope someone can help me,

 

Thank you.

Need Help

3 Replies

  • goncalogeraldes's avatar
    goncalogeraldes
    Super User
    5 years ago

    You can either incorporate everything in one single measure or reference your past measures: Whichever works better for your dataset:

    Positive Absolute Share Growth = 
var ly_sales = Calculate(SUM(Facts[Sales EURO]), Calendar[Year] = "LY")
var ty_sales = Calculate(SUM(Facts[Sales EURO]), Calendar[Year] = "TY")
var ly_mark_share = DIVIDE(ly_sales, CALCULATE(ly_sales, ALL(Products[Company])))
var ty_mark_share = DIVIDE(ty_sales, CALCULATE(ty_sales, ALL(Products[Company])))
var share__growth = ty_mark_share - ly_mark_share
var total_sales = SUM(Facts[Sales EURO])

return
IF( share_growth > 0, total_sales, 0)

    Or:

    Positive Absolute Share Growth = 
var share__growth = [Market Share Growth]
var total_sales = SUM(Facts[Sales EURO])

return
IF( share_growth > 0, total_sales, 0)

     

    Hope this answer solves your problem! If you need any additional help please tag me in your reply.
    If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
    Thanks!

    Best regards,
    Gonçalo Geraldes

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi, unfortunately, this is not giving me the right answer, the measure when added to a card returns zero. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    After some more search I managed to find out how to get the total of the measure to not be zero, though the values were correct in a table:

    m_Total 1 =
    VAR __table = SUMMARIZE('Market',[Country],"__value",[Positive Absolute Share Growth])
    RETURN
    IF(HASONEVALUE(Market[Country]),[Positive Absolute Share Growth],SUMX(__table,[__value]))
     
    Hope this helps anyone else with a similar question