Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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.
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:
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
Hi, unfortunately, this is not giving me the right answer, the measure when added to a card returns zero.