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

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:

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.

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

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

2 Likes

Hi @Melissa

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.

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

1 Like

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)?

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

.

2 Likes

Thank you @Melissa it works now 100%

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

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

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.

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?

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.

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

You can find that here.

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

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?

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.

1 Like

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)

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

3 Likes

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