TOPN 3 latest sale -other pattern

AntrikshSharma · May 1, 2021, 7:31am

3 Latest Sales by Revenue = 
CALCULATE ( 
    [P&L Revenue],
    KEEPFILTERS ( 
        TOPN (
            3,
            ALL ( 'Job category'[Financial level 1] ),
            [P&L Revenue], DESC
        )
    )
)

Matty · May 1, 2021, 10:04am

@AntrikshSharma Hi

I have this formula build into my model but I was trying to get the dynamic result based on the date filter.

3 Latest Sales by GP = SUMX(
TOPN(3, CALCULATETABLE(
SUMMARIZE(Data,Data[Completed]),ALL(‘Job category’)),
Data[Completed],DESC),
[P&L GP])

The result that I would like to get is to show the last 3 sales that happened and to have it dynamic (using date slicer)

I added your formula to my model but it will bring the total of TOP3 overall by financial level.

For GP I would like to see as it is but for revenue 07/04/2021, 02/04/2021 (where the sales occurred)

The issue that I have I can’t make it dynamically using date slicer:

If I filter the date slicer to 13/01/2021 then TOP3 will not provide me the result that I want to see, that’s why I tried to use the RankX formula but unfortunately, I am not that good as you guys. Brian did give me hints on how I should approach this topic but I am failing it

On the Test Page, you will see rank formulas that I tried to use but I wasn’t successful same on the test2 page.

Would be able to help please ?

BrianJ · May 2, 2021, 8:57am

@Matty,

Sorry - I’ve been bogged down with a couple of big projects. Will take a look on Sunday and get back to you.with a solution.

Brian

AntrikshSharma · May 2, 2021, 2:28pm

@Matty See if this helps.

3 Latest Sales by Revenue 2 =
SUMX (
    FILTER (
        CALCULATETABLE (
            VAR Temp =
                ADDCOLUMNS (
                    SUMMARIZE ( Data, 'Job category'[Financial level 1], Data[Completed] ),
                    "@PL Revenue", [P&L Revenue]
                )
            VAR TempWithoutBlank =
                FILTER ( Temp, [@PL Revenue] <> 0 )
            VAR TopPL =
                TOPN ( 3, TempWithoutBlank, [@PL Revenue], DESC )
            RETURN
                TopPL,
            REMOVEFILTERS ( 'Job category'[Financial level 1] )
        ),
        'Job category'[Financial level 1]
            IN VALUES ( 'Job category'[Financial level 1] )
    ),
    [@PL Revenue]
)

Alternate version:

3 Latest Sales by Revenue 2 =
CALCULATE (
    [P&L Revenue],
    KEEPFILTERS (
        CALCULATETABLE (
            VAR Temp =
                ADDCOLUMNS (
                    SUMMARIZE ( Data, 'Job category'[Financial level 1], Data[Completed] ),
                    "@PL Revenue", [P&L Revenue]
                )
            VAR TempWithoutBlank =
                FILTER ( Temp, [@PL Revenue] <> 0 )
            VAR TopPL =
                TOPN ( 3, TempWithoutBlank, [@PL Revenue], DESC )
            RETURN
                TopPL,
            REMOVEFILTERS ( 'Job category'[Financial level 1] )
        )
    )
)

Matty · May 2, 2021, 8:34pm

Hi @BrianJ No problem no rush.

Thank you @AntrikshSharma for your help, honestly appreciated it.

I studied your formula to have a better understanding, I added few comments, do you mind to check it?
What is the reason to add Keepfilters? This is the first time I think where I’ve seen VAR functions used inside of formula pattern.
Usually, I used and seen on yt VAR 1, VAR 2 etc Return Calculate,VAR1,VAR2 etc. whereas your pattern is completely different level. Thank you for showing me this!

3 Latest Sales by Revenue 2 v2 =
CALCULATE (
[P&L Revenue],
//Calculate revenue
//why do we use keepfilters KEEPFILTERS (
CALCULATETABLE (
VAR Temp =
ADDCOLUMNS (
SUMMARIZE ( Data, ‘Job category’[Financial level 1], Data[Completed] ),
“@PL Revenue”, [P&L Revenue]
) // Creates a table for each job category and date completed, adding extra column with revenue for that date and job
VAR TempWithoutBlank =
FILTER ( Temp, [@PL Revenue] <> 0 )
// filtering above table to remove revenue that doesnt equal to 0
VAR TopPL =
TOPN ( 3, TempWithoutBlank, [@PL Revenue], DESC )
// calcuate top 3 from above table
RETURN
TopPL,
REMOVEFILTERS ( ‘Job category’[Financial level 1] )
//remove filter on job category? so we can filter the data by job category or financial level?
)
)
)

The formula pattern nearly works meaning I can apply the date slicer filter but the results don’t give me TOP3 dates.

Thank you,

Matty

AntrikshSharma · May 3, 2021, 3:41am

@Matty CALCULATETABLE returns a list of Financeal Level 1, when these values are applied to the filter context they will overwrite the existing filter and you will only get the Total P & L of all those values, so to not overwrite the existing filter and to create an intersection between filter context outside CALCULATE and inside CALCULATE we need to use KEEPFILTERS.

REMOVEFILTERS is used so that at each cell of the visual all the Financial Level are visible otherwise TOPN will only receive 1 row containing the value visible in the current cell of the report.

Regarding the above requirement, I see you are using different columns so the granularity is also different, try to adjust the formula accordingly.

Matty · May 3, 2021, 2:52pm

Thank you @AntrikshSharma for the explanation.

I changed the financial level to job description columns, the result is exactly the same What do you suggest?

Matty · May 5, 2021, 8:22pm

Dear @BrianJ did you had time to look at it?

Thank you,

Matty

BrianJ · May 6, 2021, 2:24pm

@Matty,

I stepped away from this one when I saw @AntrikshSharma step in, but I would be glad to reengage on this and see how best to get you to a final solution.

Brian

Matty · May 6, 2021, 2:27pm

Thank you @BrianJ

I did change the table references in the formula as @AntrikshSharma highlighted but the formula still throws everything up to max date in the slicer, whereas I would like to see the last 3 transactions.

Thank you,

Matty

BrianJ · May 6, 2021, 2:30pm

@Matty,

Can you please post your latest PBIX work-in-progress?

Thanks!

– Brian

Matty · May 6, 2021, 2:31pm

Sure,
Sales Report.pbix (963.7 KB)
Please find the attached.

Thank you,

Matty

BrianJ · May 10, 2021, 5:25am

@Matty,

FYI – been working on this one for a good chunk of the evening, leveraging off the progress that you and @AntrikshSharma have made. Have it working perfectly as a DAX query, but having some difficulty collapsing that result down into a scalar measure and returning the right value in context. I feel like I’m getting pretty close to cracking it, but I need your Excel file because I think to implement the solution I’m working on, I will need a physical index column created in Power Query on the data table.

Thanks.

– Brian

AntrikshSharma · May 10, 2021, 3:13pm

@Matty You can use this

3 Latest Sales by Revenue 2 =
CALCULATE (
    [P&L Revenue],
    KEEPFILTERS (
        TOPN (
            3,
            CALCULATETABLE (
                SUMMARIZE ( Data, 'Job category'[Job category description], Data[Completed] ),
                ALLEXCEPT ( Data, 'Date'[Date] )
            ),
            [P&L Revenue]
        )
    )
)

also activate the relationship between dates and data[completed]

BrianJ · May 10, 2021, 4:01pm

@AntrikshSharma,

Nice! Much cleaner and easier to follow than the Massive Mountain O’ DAX I’ve been throwing at this problem.

Brian

EnterpriseDNA · May 11, 2021, 1:14am

Hi @Matty, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!