Sales % (yearly) - - better way to calculate?

[michellepace]

Hi there,

Audio made up 1.02% sales of the total sales in 2007. Below is the dax code I use to get to 1.02%. It is indeed correct. However, is there perhaps a more elegant(simple) way to write this measure? (the workbook is available here for download)

Sales % (yearly) =
– Create a variable that holds the current sales w.r.t any filters that may be in place
VAR currentSalesAmount = CALCULATE ( [Sales Amount], ‘Online Sales’ )

–Retrieve all transactions for the current context year. The Calendar table will be filtered due to the fact it is included in the matrx
VAR YearTransactions =
FILTER (
ALL ( ‘Online Sales’ ),
RELATED ( ‘Calendar’[Calendary Year] ) IN VALUES ( ‘Calendar’[Calendary Year] )
)

– Create a variable that holds the total sales with all filters removed
VAR YearSalesAmount =
CALCULATE ( [Sales Amount], YearTransactions )

RETURN
DIVIDE ( currentSalesAmount, YearSalesAmount )

Best wishes,
Michelle

[EnterpriseDNA]

Hi @michellepace, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

• Use the forum search to discover if your query has been asked before by another member.
• When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
  
• Use the proper category that best describes your topic
• Provide as much context to a question as possible.
• Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

[AntrikshSharma]

@michellepace

Sales % Yearly = 
DIVIDE ( 
    [Total Sales],
    CALCULATE ( [Total Sales], ALL ( Products[Category] ) )
)

[michellepace]

Hi Antriksh,
I actually cannot believe this works! It leaves me quite speechless as to why anyone would come up with such a complex solution as in the above. Thank you! Having said that, could you please explain to me why Sales % and Sales % Yearly arrive at such different results? That is, can you possibly explain why it is that your Sales % Yearly actually… works?

Sales %
= DIVIDE( [Sales Amount], CALCULATE( [Sales Amount], ALL( ‘Online Sales’ ) ) )

Sales % Yearly
= DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALL ( Products[Category] ) ))

Thank you once again,
Michelle

[AntrikshSharma]

@michellepace

When you use ALL ( Online Sales ) you remove filters from every table that connects to Online Sales using Many to 1 relationship or the LEFT JOIN in SQL terms, this is the foundation of DAX, which is also known as expanded tables which basically means every time you use a full table you are referring to the expanded version.

This is why you are able to use RELATED, otherwise RELATED is useless if expanded version of the table isn’t being used because RELATED only acts like a ID Card/gate pass to allow access to columns of an expanded table.

Behind the scenes Sales is a big fat table with all the columns of Sales, Calendar, Promotions, Customers, Stores, and Products

Read more here: Expanded tables in DAX - SQLBI
Red cross means filter is removed from whole model when you use ALL ( OnlineSales )

When you use ALL ( Product[Category] ) You only remove filters from Products[Category] and any other filter from any table will still be active.

In case of a Snow Flake schema like the one below, Products expands to itself plus ProductsSubcategory and ProductsCategory. So If I use ALL ( Products ) I would remove filters from Products but also from Category and Subcategory as well. And Similarly Sales would expand to itself, plus Products, ProductsSubcategory, and ProductsCategory and other tables following Many to 1 relationship

There are ways to restore filters over ProductsSubcategory and Category using following:

I you use ALLEXCEPT you will notice that intellisense suggest all the columns of expanded tables related to the base table that you mention in the first argument of ALLEXCEPT

or

Measure 3 = 
CALCULATE ( 
    [Total Sales],
    ALL ( Products ), 
    VALUES ( ProductsSubcategory )
)

If you use VALUES ( ProductsSubcategory ) Then you will also restore filters over ProductsCategory due to expanded tables.

In the Measure 3 the following happens, first you remove Filters from expanded Products Table and then you restore filters over expanded ProductSubcategory table.

[BrianJ]

@AntrikshSharma,

Absolutely awesome explanation.

• Brian

[michellepace]

Thank you very much Antriksh - I honestly had no idea about the concept of expanded tables!! Thank you very the lovely visual explanation, its a big help. (I love snagit )

[JarrettM]

@AntrikshSharma,

This would be a great example to do a video on!

Thanks
Jarrett

[michellepace]

I’d love a video! My understanding is of Antriksh’s explanation is about 64% of the way there.

[sujitc1]

@AntrikshSharma,

nice explanation

Sujit