This Query related with thread "Advanced DAX Measure and Analysis Techniques"
Prompt
Answer
To tackle your issue, two measures are needed:
- Year Over Year calculation for customers with sales in the prior year.
- A TOPN function to show only top 5 of these customers in terms of sales.
Both should completely solve your problem since they will take care of the YOY and TOPN constraint respectively.
Measure 1: YOY Calculation
Here, the initial calculation should be slightly modified to calculate YOY change only for customers who had sales in both the years. This will help eliminate those customers who registered sales only in one year, hence skewing the YOY calculations.
Here's one example of how you might calculate the YOY change:
YoY_AverageSalesPrice =
CALCULATE (
AVERAGE ( Sales[Price] ),
FILTER (
ALL ( Sales ),
Sales[Year] = MAX ( Sales[Year] )
&& Sales[Customer] IN CALCULATETABLE (
VALUES ( Sales[Customer] ),
ALL ( Sales ),
Sales[Year] = MAX ( Sales[Year] ) - 1
)
)
)
This measure first determines the list of customers who had sales in the prior year and current year. It then computes the average of the Sales[Price]
for those customers.
Measure 2: TOPN function
Let's now proceed to the part which deals with displaying the top 5 customers with largest YOY changes:
Top5_Customers_YoY_ASP =
IF (
HASONEVALUE ( Customers[Customer] ),
[YoY_AverageSalesPrice],
CALCULATE (
[YoY_AverageSalesPrice],
TOPN (
5,
ALL ( Customers[Customer] ),
[YoY_AverageSalesPrice],
DESC
)
)
)
This measure checks if the context is a single customer (which can occur when it's being used in a visual where the calculation is broken down by customer). If so, it merely computes the YOY change. But if the context isn't a single customer, then it computes the YOY change but only for the five customers with the highest such changes. It does this through the TOPN function, where we select the top 5 Customers[Customer]
based on the [YoY_AverageSalesPrice]
.
Remember to set visual-level filters in your matrix to the [Top5_Customers_YoY_ASP]
measure for the customers you want to highlight.
I hope this helps in resolving your issue. Let me know if you encounter any problems with the solution. Don't hesitate to elaborate on your requirements further if there are additional constraints or requirements.
Description
Learn how to develop DAX measures in Power BI for Year Over Year (YOY) calculations and showcasing the top 5 customers with the largest YOY changes in sales. Includes examples and guidance on implementation.
More Languages Advisors
![Apache Flink Languages Advisor](https://fs.enterprisedna.co/library/icons/flink.png)
![Apache Pig Languages Advisor](https://fs.enterprisedna.co/library/icons/apache_pig.png)
![Azure Data Factory Languages Advisor](https://fs.enterprisedna.co/library/icons/azure-data-factory.png)
![C/C++ Languages Advisor](https://fs.enterprisedna.co/library/icons/c_c_plus.png)
![CouchDB Languages Advisor](https://fs.enterprisedna.co/library/icons/couchdb.png)
![DAX Languages Advisor](https://fs.enterprisedna.co/library/icons/power-bi.png)
![Excel Languages Advisor](https://fs.enterprisedna.co/library/icons/excel.png)
![Firebase Languages Advisor](https://fs.enterprisedna.co/library/icons/firebase.png)
![Google BigQuery Languages Advisor](https://fs.enterprisedna.co/library/icons/google_bigquery.png)
![Google Sheets Languages Advisor](https://fs.enterprisedna.co/library/icons/google_sheets.png)
![GraphQL Languages Advisor](https://fs.enterprisedna.co/library/icons/graphql.png)
![Hive Languages Advisor](https://fs.enterprisedna.co/library/icons/hive.png)
![Java Languages Advisor](https://fs.enterprisedna.co/library/icons/java.png)
![JavaScript Languages Advisor](https://fs.enterprisedna.co/library/icons/javascript.png)
![Julia Languages Advisor](https://fs.enterprisedna.co/library/icons/julia.png)
![Lua Languages Advisor](https://fs.enterprisedna.co/library/icons/lua.png)
![M (Power Query) Languages Advisor](https://fs.enterprisedna.co/library/icons/m.png)
![MATLAB Languages Advisor](https://fs.enterprisedna.co/library/icons/matlab.png)
![MongoDB Languages Advisor](https://fs.enterprisedna.co/library/icons/mongodb.png)
![Oracle Languages Advisor](https://fs.enterprisedna.co/library/icons/oracle.png)
![PostgreSQL Languages Advisor](https://fs.enterprisedna.co/library/icons/postgresql.png)
![Power BI Languages Advisor](https://fs.enterprisedna.co/library/icons/power-bi.png)
![Python Languages Advisor](https://fs.enterprisedna.co/library/icons/python.png)
![R Languages Advisor](https://fs.enterprisedna.co/library/icons/r.png)
![Redis Languages Advisor](https://fs.enterprisedna.co/library/icons/redis.png)
![Regex Languages Advisor](https://fs.enterprisedna.co/library/icons/regex.png)
![Ruby Languages Advisor](https://fs.enterprisedna.co/library/icons/ruby.png)
![SAS Languages Advisor](https://fs.enterprisedna.co/library/icons/sas.png)
![Scala Languages Advisor](https://fs.enterprisedna.co/library/icons/scala.png)
![Shell Languages Advisor](https://fs.enterprisedna.co/library/icons/shell.png)
![SPSS Languages Advisor](https://fs.enterprisedna.co/library/icons/spss.png)
![SQL Languages Advisor](https://fs.enterprisedna.co/library/icons/sql.png)
![SQLite Languages Advisor](https://fs.enterprisedna.co/library/icons/sqlite.png)
![Stata Languages Advisor](https://fs.enterprisedna.co/library/icons/stata.png)
![Tableau Languages Advisor](https://fs.enterprisedna.co/library/icons/tableau.png)
![VBA Languages Advisor](https://fs.enterprisedna.co/library/icons/vba.png)