Forum Discussion

cursty's avatar
cursty
Frequent Visitor
1 year ago
Solved

Calculations with virtual table

Hello everyone,   I am working with expenses dataset. This dataset contains 4 columns: the unique id, item id (which can appear multiple times in case few payments have been made for the same item)...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the reply from lbendlin and Greg_Deckler, please allow me to provide another insight.
    Hi cursty ,

    Please try the following dax.

    Perc_Rank = 
VAR _tb = CALCULATETABLE(SUMMARIZECOLUMNS('Expenses'[item_id],"Expense",[Expenses]),ALL())
VAR _expenses = VALUE([Expenses])
VAR _lower = IF(COUNTROWS(FILTER(_tb, [Expense]<_expenses))=BLANK(),0,COUNTROWS(FILTER(_tb, [Expense]<_expenses)))
VAR _higher = IF(COUNTROWS(FILTER(_tb, [Expense]>_expenses))=BLANK(),0,COUNTROWS(FILTER(_tb, [Expense]>_expenses)))
RETURN
DIVIDE(_lower,_higher+_lower,0)


    The result is as follows, hopefully it will meet your needs.


    Please see the attached pbix for reference.

    Best Regards,
    Dengliang Li

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

cursty's avatar
cursty
Frequent Visitor
1 year ago

Thank you for suggestion Greg_Deckler lbendlin 

 

I used your measure in my dashboard, but unfortunately it still doesn't resolve my problem as I'm receiving value 0 for the item with highest expenses and blanks for the rest of them.

 

Below, I highlighted what I've changed in your original code

PERCENTILERANK.INC =
VAR maxdt = MAX('Calendar'[Date])
VAR mindt = EDATE(maxdt, -36) + 1
VAR itm = ADDCOLUMNS(VALUES(Expenses[item_id]), "tv", CALCULATE(SUM(Expenses[paid_amount]), Expenses[payment_date] IN CALENDAR(mindt, maxdt)))
VAR __Value =  MAXX(SUMMARIZE(Expenses, Expenses[item_id], "Amount", SUM(Expenses[paid_amount])), [Amount])


RETURN
    IF(
        __Value IN SELECTCOLUMNS(itm,"Values",[tv]),
                VAR __NumLower = COUNTROWS(FILTER(itm,[tv] < __Value))
                VAR __NumHigher = COUNTROWS(FILTER(itm, [tv] > __Value))
                VAR __Rank = __NumLower / (__NumLower + __NumHigher)
            RETURN IF(ISBLANK(__Rank),0,__Rank),
                VAR __Lower = MAXX(FILTER(itm,[tv] < __Value),[tv])
                VAR __Higher = MINX(FILTER(itm,[tv] > __Value),[tv])
                VAR __LowerNumLower = COUNTROWS(FILTER(itm,[tv] < __Lower))
                VAR __LowerNumHigher = COUNTROWS(FILTER(itm,[tv]> __Lower))
                VAR __LowerRank = __LowerNumLower / (__LowerNumLower + __LowerNumHigher)
                VAR __HigherNumLower = COUNTROWS(FILTER(itm,[tv]< __Higher))
                VAR __HigherNumHigher = COUNTROWS(FILTER(itm,[tv] > __Higher))
                VAR __HigherRank = __HigherNumLower / (__HigherNumLower + __HigherNumHigher)
            RETURN
                __LowerRank + ( __Value - __Lower ) / (__Higher - __Lower ) * ( __HigherRank - __LowerRank )
    )