To get YoY% of top5+other

[Afthab]

Hi All,

I have already made a measure to get the Top5+Other. Now I need a measure that calculates the Yoy variance, according to the change in slicer. So, If I choose 2023, I get the products in Top5+other products in 2023 compared to the same products in 2022. Attached is a screenshot, to give some idea of the data. The Top5 of 2023, wont necessarily be the Top5 of last year.

Here is the Dax measure I used to calculate the Top5+other:

TopV = 

VAR TopNValue = 5

VAR AlwaysProductsList = {"Product Names"} 

VAR TopNPlusOurProducts = 

    UNION (
        TOPN ( TopNValue, ALLSELECTED ( TopProducts ) , [SV] ) , 
        FILTER ( ALL ( 'TopProducts' ) , TopProducts[Product] IN AlwaysProductsList)
    )

VAR AllValue = CALCULATE ( [SV] , ALLSELECTED ( TopProducts ) )

VAR OtherValue = AllValue - CALCULATE ( [SV] , TopNPlusOurProducts )

VAR TopNPlusOurProductsValue = CALCULATE ( [SV] , KEEPFILTERS ( TopNPlusOurProducts ) )

VAR CurrentProduct = SELECTEDVALUE ( TopProducts[Product] )

RETURN

IF ( CurrentProduct = "Other" , OtherValue , IF ( CurrentProduct IN AlwaysProductsList , TopNPlusOurProductsValue , TopNPlusOurProductsValue )) 

Please let me know if you need more info

Thanks

[123abc]

It looks like you have already created a measure (TopV) to calculate the Top5+Other products. Now, you want to create a Year-over-Year (YoY) variance measure based on the selected year in a slicer.

Assuming you have a Date table in your model and the slicer is connected to this Date table, you can create a YoY variance measure using DAX. Here's an example of how you can modify your existing measure to include YoY variance:

YoY_Variance =
VAR SelectedYear = SELECTEDVALUE('Date'[Year])

VAR TopNValue = 5
VAR AlwaysProductsList = {"Product Names"}

VAR TopNPlusOurProducts =
UNION (
TOPN ( TopNValue, ALLSELECTED ( TopProducts ) , [SV] ) ,
FILTER ( ALL ( 'TopProducts' ) , TopProducts[Product] IN AlwaysProductsList)
)

VAR AllValue = CALCULATE ( [SV] , ALLSELECTED ( TopProducts ) )

VAR OtherValue = AllValue - CALCULATE ( [SV] , TopNPlusOurProducts )

VAR TopNPlusOurProductsValue = CALCULATE ( [SV] , KEEPFILTERS ( TopNPlusOurProducts ) )

VAR CurrentProduct = SELECTEDVALUE ( TopProducts[Product] )

VAR YoY_Value =
CALCULATE(
[SV],
SAMEPERIODLASTYEAR('Date'[Date])
)

VAR YoY_Variance_Result = YoY_Value - [SV]

RETURN
IF (
CurrentProduct = "Other",
OtherValue,
IF (
CurrentProduct IN AlwaysProductsList,
TopNPlusOurProductsValue,
YoY_Variance_Result
)
)

This measure, YoY_Variance, calculates the YoY variance for the selected product. If "Other" is selected, it returns the OtherValue as before. If a product from the AlwaysProductsList is selected, it returns the TopNPlusOurProductsValue as before. For other products, it calculates the YoY variance.

Make sure to adjust the measure names and column names according to your data model. Also, ensure that your Date table is properly set up with a relationship to the fact table containing the sales data.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Afthab]

Thank you for your reply @123abc,

However, I'm still not getting the correct values. I have attached the ss of the current year and previous year.

[123abc]

To troubleshoot and refine the measure, follow these steps:

1. Verify Slicer Interaction: Ensure that your measure is correctly interacting with the slicer. You should use the SELECTEDVALUE function to get the selected year from the slicer. Double-check that the slicer is linked to the correct date column in your data model.

2. Check Date Context: Make sure that the date context is applied correctly in your measures. In your calculations involving years, use the appropriate date column or hierarchy.

3. Review YoY Calculation: Verify that your YoY calculations are accurate. Ensure that you are comparing values for the same set of products between the selected year and the previous year.

4. Debugging with DAX: You can use the DAX Studio or other tools to debug your DAX measures. Break down the measure into smaller components and evaluate them individually to identify where the issue might be.

Here's an example of how you can simplify your measure for debugging purposes:

YoY_Variance_Debug =
VAR SelectedYear = SELECTEDVALUE('YourDateTable'[YearColumn])
VAR PreviousYear = SelectedYear - 1

VAR TopNValue = 5
VAR AlwaysProductsList = {"Product Names"}

VAR TopNPlusOurProducts =
UNION (
TOPN ( TopNValue, ALLSELECTED ( TopProducts ) , [SV] ) ,
FILTER ( ALL ( 'TopProducts' ) , TopProducts[Product] IN AlwaysProductsList)
)

VAR TopNPlusOurProductsValue = CALCULATE ( [SV] , KEEPFILTERS ( TopNPlusOurProducts ), 'YourDateTable'[YearColumn] = SelectedYear)

VAR TopNPlusOurProductsValuePreviousYear = CALCULATE ( [SV] , KEEPFILTERS ( TopNPlusOurProducts ), 'YourDateTable'[YearColumn] = PreviousYear)

RETURN
TopNPlusOurProductsValue - TopNPlusOurProductsValuePreviousYear

Use this simplified measure and evaluate each variable individually to see if the values are as expected. This process can help you identify where the issue lies.

If you can provide specific details about the incorrect values you are getting or share parts of your data model, I can offer more targeted assistance.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.