Earlier function on the fly

[Anonymous]

Hi all,

I'm trying to create rank calculation on PBI.
I'm able to do that with earlier function which only allows create on column. When I created on column It is not serve to purpose completely. 
Let me show an example.

My dataset on the left side. I created a column like this.

Group Rank = COUNTROWS(FILTER ('Test',Test[Date] = EARLIER (Test[Date]) && Test[Amount] >EARLIER(Test[Amount] )))+ 1

It works well when I select 1 month from date filter. But when I select 2 months from date filter It shows ranks separately. (for each month)
I want to calculcate rank for sum of 2 months value.

I know It is normal because we are creating a column and write data directly to this column. 
Actually what I want to ask is there any option to create this calculation with measure ?

PBIX FILE

 

[Anonymous]

Thank you for all @AlB , @MFelix and @amitchandak 

It was absolutely working fine for this scenario but there is another point which I forget to mention on there.

All of you guys used RANKX function but actually I want to find reciprocity to EARLIER function. You are all right, RANKX looks like fit for this scenario but in I'm using EARLIER function with other calculations. Like cumulative sum.

Let me show you my main problem.

I want to find customers % amount for total amount.
Then I want to cumulative sum customer % amount from top to bottom rank.
Then make filter on Cumulative Amount % column. For example show the values less than %80

It is working fine for one month but not working well as I mentioned on my first message for more than one month.

I created Group Amount, Group Rank, Amount % and Cumulative Amount % on column and I used EARLIER function most of this calculations. 
Thats the point which I'm looking for a solution.
By the way If you have another solution to make this cumulative amount % calculation It is fine for me no matter.

[MFelix]

Hi @Anonymous ,

The best option for % in my opinion is to do it with measures, because when you make them has columns on your datatables you then loose the flexibility of having the filters calculation.

If you do the % as measures then you can do the rank based on those measures making a temporary table on your measure.

Can you please provide the way you are calculating the %s.

Please @AlB  and @amitchandak  if you have any other ideas please share them.

[Anonymous]

Hi @MFelix ,

First of all I find the total amount for customers for each month. 

Group Amount = CALCULATE (
    SUM( PCC_Analysis2[Amount] ),
    FILTER (
        'PCC_Analysis2',
        'PCC_Analysis2'[Tarih] = EARLIER ( 'PCC_Analysis2'[Tarih] )))

Then I create calculation rank for customers amount 

Group Rank =
COUNTROWS (
    FILTER (
        'PCC_Analysis2',
        'PCC_Analysis2'[Tarih] = EARLIER ( 'PCC_Analysis2'[Tarih] )
            && PCC_Analysis2[Amount %] > EARLIER ( PCC_Analysis2[Amount %] )
    )
) + 1

 
Then I find % Amount for each customer with this divide amount and total amount

Amount % = PCC_Analysis2[Amount] / PCC_Analysis2[Group Amount]

Finally I'm making this cumulative sum calculation.

Cumulative Amount % = 
CALCULATE (
    SUM( PCC_Analysis2[Amount %] ),
    FILTER (
        'PCC_Analysis2',
        'PCC_Analysis2'[Tarih] = EARLIER ( 'PCC_Analysis2'[Tarih] ) && PCC_Analysis2[Group Rank] <= EARLIER(PCC_Analysis2[Group Rank])))

This is the final result

[v-rzhou-msft]

Hi @Anonymous 

You may try to build a measure to achieve your goal.

Firstly, build a slicer table.

Slicer = VALUES('PCC_Analysis2'[Tarih])

Measure:

Amount% = 
VAR _sel =
    ALLSELECTED ( Slicer[Tarih] )
VAR _GroupAmount =
    IF (
        ISFILTERED ( Slicer[Tarih] ),
        CALCULATE (
            SUM ( PCC_Analysis2[Amount] ),
            FILTER ( ALL ( 'PCC_Analysis2' ), 'PCC_Analysis2'[Tarih] IN _sel )
        ),
        CALCULATE (
            SUM ( PCC_Analysis2[Amount] ),
            FILTER (
                ALL ( 'PCC_Analysis2' ),
                'PCC_Analysis2'[Tarih] = MAX ( PCC_Analysis2[Tarih] )
            )
        )
    )
VAR _Amount =
    IF (
        ISFILTERED ( Slicer[Tarih] ),
        CALCULATE (
            SUM ( PCC_Analysis2[Amount] ),
            FILTER ( 'PCC_Analysis2', 'PCC_Analysis2'[Tarih] IN _sel )
        ),
        SUM ( PCC_Analysis2[Amount] )
    )
RETURN
    _Amount / _GroupAmount

Cumulative Amount % = 
IF (
    [Amount%] = BLANK (),
    BLANK (),
    IF (
        ISFILTERED ( Slicer[Tarih] ),
        SUMX (
            FILTER (
                ALLSELECTED ( PCC_Analysis2 ),
                PCC_Analysis2[Tarih] IN ALLSELECTED ( Slicer[Tarih] )
                    && PCC_Analysis2[Rank] <= MAX ( PCC_Analysis2[Rank] )
            ),
            [Amount%]
        )
    )
)

Result:

As Default:

Select two Tarih in Slicer.

You can download the pbix file from this link: Earlier function on the fly

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Hi @v-rzhou-msft ,

Thank you for your helps.

The Amount measure is working fine which you created but the cumulative amount not working as it's supposed to be.

You are making group the values by Rank. When we do this, different customers can be found in the same group.

For ex, in your example customer 0003 and 0005 were grouped. This is different from our logic.

My expected result should be contain Customer, amount and Cumulative amount. not rank. and shoul be like this.

I tried to change Cumulative Amount % dax formula. It must not be contain Rank column. Because Rank column is static for each month. We need on the fly rank formula or something different.

I tried to change

&& PCC_Analysis2[Rank] <= MAX ( PCC_Analysis2[Rank] )

part of your dax with Amount % but it not accepting measures.

Do you have any idea about this ?

[v-rzhou-msft]

Hi @Anonymous 

Due to there may be some same results in Amount column when you select more than one tarihs.

So, calculate Cumulative Amount by rank is not a good way, I advice you calculate it by adding an ID column.

ID = SWITCH(PCC_Analysis2[Customer],"0001",1,"0002",2,"0003",3,"0004",4,"0005",5)

New Measure:

Amount% =
VAR _Sel =
    ALLSELECTED ( Slicer[Tarih] )
VAR _Amount =
    IF (
        ISFILTERED ( Slicer[Tarih] ),
        SUMX (
            FILTER (
                PCC_Analysis2,
                PCC_Analysis2[Customer] = MAX ( PCC_Analysis2[Customer] )
                    && PCC_Analysis2[Tarih] IN _Sel
            ),
            PCC_Analysis2[Amount]
        ),
        SUMX (
            FILTER (
                PCC_Analysis2,
                PCC_Analysis2[Customer] = MAX ( PCC_Analysis2[Customer] )
            ),
            PCC_Analysis2[Amount]
        )
    )
VAR _Total =
    IF (
        ISFILTERED ( Slicer[Tarih] ),
        SUMX (
            FILTER ( ALL ( PCC_Analysis2 ), PCC_Analysis2[Tarih] IN _Sel ),
            PCC_Analysis2[Amount]
        ),
        SUMX ( ALL ( PCC_Analysis2 ), PCC_Analysis2[Amount] )
    )
RETURN
    _Amount / _Total

Cumulative Amount % = 
    IF (
        ISFILTERED ( Slicer[Tarih] ),
        SUMX (
            FILTER (
                ALL(PCC_Analysis2 ),
                PCC_Analysis2[Tarih] IN ALLSELECTED ( Slicer[Tarih] )
                    && PCC_Analysis2[ID] >= MAX( PCC_Analysis2[ID] )
            ),
            [Amount%]
        ))

Result:

You can download the pbix file from this link:  Earlier function on the fly

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Hi @v-rzhou-msft,

I'm sorry, I guess I'm having trouble explaining myself.

I couldn't understand why did you create a ID column for customers. By the way it is not a dynamic calculation how can I apply this method to all my dataset.

I don't want to sort my customers by their customer ID.  My requirement is so simple.
Earlier function is okey for me but it is working on column I need to work with measure.
The amount% measure is working fine which you created.
In this case we just need cumulative sum for amount% when we sort amount% from big to small.
Your result is sorting by customer.

Expected Result Examples

I really need help for this case. I couldn't find any solution. Thank you for your endless helps.

[MFelix · Accepted solution]

Hi @Anonymous ,

You need to create the following measures, be aware that I'm divinding this in several measure so you can have the calculations checked.

Amount = SUM(PCC_Analysis2[Amount])

Rank_Values = RANKX(ALLSELECTED(PCC_Analysis2[Customer]); CALCULATE([Amount]) + + INT ( CALCULATE ( MIN ( PCC_Analysis2[Customer]) ) ) / 100000)

Cumulative Total based on Rank = 
           CALCULATE([Amount];
                     TOPN([Rank_Values];ALLSELECTED(PCC_Analysis2[Customer]);[Amount] + INT ( CALCULATE ( MIN ( PCC_Analysis2[Customer]) ) ) / 100000 )
           )

% of Products Running Total = 
         DIVIDE([Cumulative Total based on Rank];CALCULATE([Amount];ALLSELECTED(PCC_Analysis2[Customer])))

The idea here is to pick up the TOPN lines that are equal to the ranking.

Adding the + INT ( CALCULATE ( MIN ( PCC_Analysis2[Customer]) ) ) / 100000 allows to make the equal values to be different.

Be aware that I'm making a relationship between slicer and fact table.

@v-rzhou-msft  Using you PBIX file thanks in advance

Check PBIX file attach.

[AlB]

Hi @Anonymous 

Instead of a calculated column, use a measure on the last visual you are showing:

MeasureRank = RANKX(ALL(Test[Customer]),CALCULATE(SUM(Test[Amount])))

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[MFelix]

Hi @Anonymous 

Try the following measure:

Measure = RANKX( ALLSELECTED( Test[Customer] ) ; CALCULATE( SUM( Test[Amount] ) ) )

Has you see below result is the same for all the data and differs when you select dates:

[amitchandak]

@Anonymous , I think you need a measure rank here. I think you have created a column rank.

Rankx(All(Table[customer]), calculate(sum(Table[Amount])))

or
Rankx(All(Table[Month],Table[customer]), calculate(sum(Table[Amount])))

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415