Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Earlier function on the fly

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....
Need Help
Show and Tell
  • MFelix's avatar
    MFelix
    5 years ago

    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.

     

    Anonymous  Using you PBIX file thanks in advance

     

    Check PBIX file attach.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous ,

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 ?

 



 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous,

      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's avatar
        MFelix
        Super User
        5 years ago

        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.

         

        Anonymous  Using you PBIX file thanks in advance

         

        Check PBIX file attach.