DAX for percentage of day from a week with year Slicer

[DebbieE]

I think I have managed to figure out creating a matrix of % Order quanity over the days of the week

I added Year Week to the SQL Dim Date Table. For example

202101

202152

(Padding out the weeks 1 to 9 and changing back to number)

And then created a DAX Query based on a question I asked yesterday

Order Quantity by Week % =

Var varOrderQtyWeek = CALCULATE(SUM(FactResellerSales[Order Quantity]), FILTER(ALL(DimDate), DimDate[Year Week]))

RETURN DIVIDE(SUM(FactResellerSales[Order Quantity]), varOrderQtyWeek))

And without a slicer it works fine

However as soon as I introduce the year slicer (I Only want Sundays in 2021 for example it fails to tell the story i need

So its right, in the year selected for accessories we had 31% of sales, but I want that specified year to be the range its looking at so it comes out as 100%.

I have tried ALLEXCEPT, ALLSELECTED, and nothing has worked. How do i update the DAX above so it resets the range to just calendar year and gives me the percentage of quanity sold on the Monday in the week based on just the year?

[VahidDM]

Hi @DebbieE 

Try this:

Order Quantity by Week % =
VAR varOrderQtyWeek =
    ADDCOLUMNS (
        CALCULATETABLE ( VALUES ( DimDate[Year Week] ), REMOVEFILTERS ( DimDate[Day Nme] ) ),
        "SumQ", SUM ( FactResellerSales[Order Quantity] )
    )
VAR _WQ =
    SUMX ( varOrderQtyWeek, [SumQ] )
VAR _DWQ =
    SUM ( FactResellerSales[Order Quantity] )
RETURN
    _DWQ / _WQ

DimDate[Day Nme] = column you used in the matrix's columns.

If it does not work, can you share a sample of your data?

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[DebbieE]

The above didnt work