cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mateoc15
Helper I
Helper I

Recalculate max based on filters

I am trying to create a gauge that shows the current week vs. the 13 week maximum. If in the last 13 weeks the maximum weekly value is 20 and the current week is 15 then the gauge would be 75% "full". It's working as expected until I apply filters. The value is filtered correctly, but the 13 week maximum is not recalculating. Here's what I'm currently using that is problematic:

 

Visitors - Weekly Max All Time = 
MAXX(
   SUMMARIZE(ALL('Traffic'), 
   'Date'[Week Of], 
   "DistinctCookie", 
   DISTINCTCOUNT(Traffic[cookie_id])), 
[DistinctCookie])

I tried this ALLEXCEPT filter but it behaves the same:

Visitors - Weekly Max All Time = 
MAXX(
    SUMMARIZE(ALL('Traffic'), 
    'Date'[Week Of], 
    "DistinctCookie", 
    CALCULATE(DISTINCTCOUNT(Traffic[cookie_id]), ALLEXCEPT('Traffic', 'Date'[Week Of]))), [DistinctCookie])

Placing the ALLEXCEPT as the first parameter of the SUMMARIZE doesn't seem to work either.


Any help appreciated. Thank you!

1 ACCEPTED SOLUTION

I finally got it.  Although I'm still not 100% sure why, this works, based on some unrelated tinkering with this article about ADDCOLUMNS.

 

The old max value calculation:

 

Visitors - Weekly Max All Time = 
    MAXX(
        SUMMARIZE(
                ALL('Traffic'), 
                'Date'[Week Of],
                "DistinctCookie", 
            DISTINCTCOUNT(Traffic[cookie_id])),
    [DistinctCookie])
    

 


The new max value calculation:

 

Visitors - Weekly Max All Time = 
    MAXX(
        ADDCOLUMNS(
            SUMMARIZE(
                ALL('Traffic'), 
                'Date'[Week Of]),
            "DistinctCookie", 
            CALCULATE(DISTINCTCOUNT(Traffic[cookie_id]))),
    [DistinctCookie])

 

The scope/context of the SUMMARIZE is just different, and a CALCULATE without a second parameter.

@amitchandak Maybe you can help explain why it makes a difference?

 

gauge_3.pnggauge_4.png

 

It's also important to note that any date filters must be on the "Week Of" value from the Date table, not any other field such as the exact date or month, etc.

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@mateoc15 , You need rolling 91 Days or 13 weeks for week you can week rank in table have measure like given below

 

column in date table

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

 

measures
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
Last 13 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-13 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

 

refer my blogs if they can help

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

@amitchandak Maybe I wasn't clear, I'm sorry.  The date is not the problem.  I have that figured out via a "Week Of in the last 13 calendar weeks" filter (which the user can't change).  The problem is the measure for the max value.  The 1.09M is total for all records, but if I filter on any dimension in the Traffic table I want it to recalculate the max value, applying that filter across the board.  Suppose the filter is favorite color.  If I select blue, and if the maximum weekly value is only 300k people with a favorite color of blue, then the high end of the gauge should show 300k and the value should show however many people have a favorite color of blue in the current week (maybe 250k or something as an example).  These filters are user-selected, not via DAX.

@mateoc15 , Allexpect will not work with summarize. You can use addcolumn on top summarize to make it work

 

or try like

 

Visitors - Weekly Max All Time =
MAXX(values('Date'[Week Of]) ,
CALCULATE(DISTINCTCOUNT(Traffic[cookie_id]), ALLEXCEPT('Traffic', 'Date'[Week Of])))

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

I finally got it.  Although I'm still not 100% sure why, this works, based on some unrelated tinkering with this article about ADDCOLUMNS.

 

The old max value calculation:

 

Visitors - Weekly Max All Time = 
    MAXX(
        SUMMARIZE(
                ALL('Traffic'), 
                'Date'[Week Of],
                "DistinctCookie", 
            DISTINCTCOUNT(Traffic[cookie_id])),
    [DistinctCookie])
    

 


The new max value calculation:

 

Visitors - Weekly Max All Time = 
    MAXX(
        ADDCOLUMNS(
            SUMMARIZE(
                ALL('Traffic'), 
                'Date'[Week Of]),
            "DistinctCookie", 
            CALCULATE(DISTINCTCOUNT(Traffic[cookie_id]))),
    [DistinctCookie])

 

The scope/context of the SUMMARIZE is just different, and a CALCULATE without a second parameter.

@amitchandak Maybe you can help explain why it makes a difference?

 

gauge_3.pnggauge_4.png

 

It's also important to note that any date filters must be on the "Week Of" value from the Date table, not any other field such as the exact date or month, etc.

mateoc15
Helper I
Helper I

gauge_1.pnggauge_2.png

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!