Calculating a measure based on conditional result from another measure

[Anonymous]

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.

[Anonymous]

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

[goncalogeraldes]

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]

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