Percent Of Total For Top N Products and W&W Change

[MAAbdullah47]

Dear Experts
I need your help in Sloving (3) Issues :

Issue (1)

I have Top N Products listed in the Table as it shows in the picture , My Problem in the Sale % measure I want it to be dynamic to reflect the caculation of (%) of total (In card) either from (Week Date) or (Category ) Or Both of them , How can I do this ?

Issue (2)

I need to calculate Week on week change for the sales I wrote a measure (PW Total Sale)

But it is not working what sort of change shall I do to make it display the W&W change on the card above?

Issue (3)

I did Filter top 3 products with a row for others please refer to the following link:

Filter top 3 products with a row for others using DAX in Power BI

My Question is if I need to pass any of the product names in the TopN table to another report, how can I pass it to the column (Product Name) in The Product table in the Drill through report?

Note: I attached the pbix file with this post.

Looking forr answers.
C0120 - Filter top 3 products with a row for others using DAX in Power BI -EDNA.pbix (2.0 MB)

[Melissa]

Hi @MAAbdullah47,

Please review forum guidelines and limit the number of questions in a single post.

Issue 1, Percent of Total

Sales % v2 = 
VAR _Sales = [Total Sales]
VAR _Other = CALCULATE( [Total Sales], ALLSELECTED( 'Date'[Week Start Date] ), ALLSELECTED( 'Product'[Category] ), REMOVEFILTERS( 'Product'[Product Name] ))
VAR _Result = DIVIDE( _Sales, _Other )
RETURN
    _Result

.
Issue 2, week on week change

First there’s an issue with your Date table.

You need to pass the second argument as well: Week Number = WEEKNUM(‘Date’[Date], 2)
Next I added this column to the Date table: WeeknYear = ‘Date’[Year] * 100 + ‘Date’[Week Number]

And created this measure.

W&W Change On Sales v2 = 
VAR LastOrder = CALCULATE( MAX( Sales[Order Date] ), ALL( Sales ))
VAR CurrentWeek = IF( ISFILTERED( 'Date'[Week Start Date] ), MAX( 'Date'[WeeknYear] ), YEAR( LastOrder )*100 + WEEKNUM( LastOrder, 2)) 
VAR PrevWeek = CALCULATE( MAX( 'Date'[WeeknYear] ), FILTER( ALL( 'Date' ), 'Date'[WeeknYear] < CurrentWeek ))
VAR _SalesCW = 
    CALCULATE( [Total Sales],
        FILTER( ALL( 'Date' ),
            'Date'[WeeknYear] = CurrentWeek
        )
    )
VAR _SalesPW = 
    CALCULATE( [Total Sales],
        FILTER( ALL( 'Date' ),
            'Date'[WeeknYear] = PrevWeek
        )
    )
VAR _Result = DIVIDE(_SalesCW-_SalesPW,_SalesPW,0)
RETURN
    _Result

.
Issue 3, please create a new thread for that one as it’s also not covered by the subject of this topic

I hope this is helpful.

[MAAbdullah47]

@Melissa

And created this measure.

Hi @Melissa
Thank you for your help

Issue No 1 Solved 100%
Issue No 2 Still Have some error In the Numbers it needs re-check , Please check the
Screen-shot below:

For example in the second line of week 1/7/2007 Should be (-67 %).

For Issue 3 I posted a new Thread , I’m also uploaded the file.

C0120 - Filter top 3 products with a row for others using DAX in Power BI -EDNA.pbix (2.0 MB)

[Melissa]

Hi @MAAbdullah47,

The figures will add up when you fix the issue with the Date table, as described in my previous post.
I hope this is helpful.

[MAAbdullah47]

Ok @Melissa Now it works , I forget to put 2 (in Weeknum function) which means Start on Monday but I have some question Our country the Business week start on (Sunday) how can we align it with this case (Week start in Sunday)?

[Melissa]

Hi @MAAbdullah47,

For weeks starting on sunday, make these changes to your Dates table.

.

I hope this is helpful

[MAAbdullah47]

Thank you @Melissa it works now 100%

[MAAbdullah47]

Hi @Melissa can you look at my (3) Issue in another forum Post?
Thank you

[MAAbdullah47]

Hi @Melissa again
I noticed small problem on the W&W Change Please check the below screen-shots:

When the week start from the Next year or the previous it gives a wrong calculation , could you help me how to solve this?

I re-attached the file again

C0120 - Filter top 3 products with a row for others using DAX in Power BI -EDNA.pbix (2.0 MB)

[Melissa]

Hi @MAAbdullah47,

Please check the week logic in your calendar at year start- and end. How should your weeks run?

If you set up your calendar correctly, that issue will be solved.

[MAAbdullah47]

Thank you @Melissa

I fixed it on the attached file in my previous post , I did the same practicse in another file I got the following results:

When I did Investigation about the week number, I checked there is a problem in the week number ordering:

The formula of Week start date , week number and weeknyear are the same:

Week Number = WEEKNUM(‘Dates’[Date],1)
Week Start Date = Dates[Date]-WEEKDAY(‘Dates’[Date],1)+1
WeeknYear = ‘Dates’[Year] * 100 + ‘Dates’[Week Number]

This is happended for end of Year 2020 and beginning of 2021 , What do you think?

[Melissa]

This is a known issue. That’s why it’s important to incorporate the week logic appropriate for your business.

The extended date table eDNA uses is an ISO 8601 calendar for example.

[MAAbdullah47]

@Melissa

The extended date table eDNA uses is an ISO 8601 calendar for example.

Hi @Melissa thank you again how we set the calender to (ISO 8601)?

[Melissa]

You can find that here.

Extended Date Table (Power Query M function)

Hi everyone, To make it easier to find I’ve placed the updated Power Query M code function for the extended Date Table / Dates Table in the category where it belongs… I hope you’ll find it useful and if there are any questions, just reach out and let me know! // August 2022 There has been an overhaul of the M code from when it was initially shared. This affects column names and values. In the Analyst Hub (follow this link) you can copy a version of Extended Date table which returns a r…

Note that this calendar has weeks starting on Monday. Do a Google search for all specifications.

[MAAbdullah47]

Hi @Melissa

Just to make sure I understan the case correctly , shall I edit the current Date (calendar) table with the (M-Query) same as inside the link you mentioned it on the previous post?

[Melissa]

Again that depends on what you need…

You’ve said your weeks start on Sunday and in an ISO 8601 calendar weeks start on Monday - so this is not an appropriate date table for your needs but does illustrate how you can set up a date table that meets your requirements.

If you need help setting up an appropriate date table, please create a new topic and clearly state your week logic requirements.

[MAAbdullah47]

hi @Melissa
I set back everything as you recommeds :
Week Number = WEEKNUM(‘Dates’[Date],2)
Week Start Date =
Dates[Date]-WEEKDAY(‘Dates’[Date],2)
WeeknYear = ‘Dates’[Year] * 100 + ‘Dates’[Week Number]

I didn’t make the Case as it is correspond to my Business, I make it as Monday Each week start , but still wrong results come when It goes from one year to the next , what we shall do next?
C0120 - Filter top 3 products with a row for others using DAX in Power BI -EDNA.pbix (2.0 MB)

[Melissa]

Hi @MAAbdullah47

This “problem” will persist until you incorporate logic in your calendar to deal with that.

Please take some time to examine the Extended Date table, you will notice that days belonging to a certain calendar year can be assigned to a week number in another year.
That solves this “issue” and note that this is an ISO 8601 type date table, so you have to design week number logic around the year start and end that meet your business requirements.

If you need assistance, create a new topic with all week logic requirements accompanied by a file and examples

[MAAbdullah47]

Thank you @Melissa Ok , I’ll post new Topic , appreciated.