Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

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.

3 REPLIES 3
Anonymous
Not applicable

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
Super User
Super User

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
Not applicable

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!