Calculations with virtual table

[cursty]

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), paid amount (amount paid for unique id on a given date) and payment date.

Last week @lbendlin helped me to create virtual tables for total amount calculations
This time, I would like to create a table (or plot) that displays the individual expense, its total amount, and its corresponding percentile. 

I started working on that using below percentile measure (orignally created by @OwenAuger  ), but it seems that the total number of expenses in range (which is important in this case) is not calculated properly.

Here's the measure:

Perc_Rank =
var maxdt = max('Calendar'[Date])
var mindt = edate(maxdt,-36)+1
var items = ADDCOLUMNS(VALUES(Expenses[item_id]),"total_amount",CALCULATE(sum(Expenses[paid_amount]),Expenses[payment_date] IN CALENDAR(mindt,maxdt)),"total_items",calculate(DISTINCTCOUNT(Expenses[item_id]),Expenses[payment_date] IN CALENDAR(mindt,maxdt)))
var RankArgument = MAXX(items, [total_amount])
var TotalCount = MAXX(items, [total_items])
var NumberOfLess = FILTER(items, [total_amount] < RankArgument)
var NumberOfGreater = FILTER(items, [total_amount] >= RankArgument)
var RankLower = COUNTROWS(NumberOfLess)
var NumberLower = MAXX(NumberOfLess, MAXX(items, [total_amount]))
var NumberUpper = MAXX(NumberOfGreater, MAXX(items, [total_amount]))
var PercentRankArgumentRank = RankLower + 1
var InterpolationFraction = DIVIDE(RankArgument - NumberLower, NumberUpper - NumberLower)
var RankInterpolated = RankLower + InterpolationFraction * (PercentRankArgumentRank - RankLower)
var PercentRankOutput = DIVIDE(RankInterpolated - 1, TotalCount - 1)
RETURN PercentRankOutput

That's the output table:

And here's the output of my TotalCount variable

I would appreciate any help in this matter.
Many thanks in advance

[Anonymous · Accepted solution]

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.

[lbendlin]

What is your expected result and why are you not using the RANK function?

[cursty]

I overcomplicated this formula. I was trying use RANKX, but I've been getting 1 for every item on the list. I am actually looking for the equivalent of the Excel PERCENTRANK.INC formula. My expected output is shown below:

Item ID	Amount	Perc
item-1	1300	50%
item-2	200	0%
item-3	4500	100%

[lbendlin]

@Greg_Deckler Did you cover that in your mapping table?

[Greg_Deckler]

@lbendlin @cursty Yes I did actually. I created a DAX equivalent Quick Measure for it.

P-Q Excel to DAX Translation - Microsoft Fabric Community

PERCENTILERANK - Microsoft Fabric Community

[cursty]

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 )

    )

[cursty]

I am not sure if I understood your question, but for this measure I didn't use any mapping besides Calendar Table, which is needed to define the dates range.

Below is more simple formula with RANKX function (user for var 'ranking' which is returning me 1 for every item on the list)

Perc_Rank2 =

var maxdt = max('Calendar'[Date])

var mindt = edate(maxdt,-36)+1

var items =  SUMMARIZE(FILTER(Expenses, Expenses[payment_date] IN CALENDAR(mindt,maxdt)), Expenses[item_id], "Value", SUM(Expenses[paid_amount]))

var currentvalue =  CALCULATE(SUM(Expenses[paid_amount]), Expenses[payment_date] IN CALENDAR(mindt, maxdt))

var total = COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(ALLSELECTED(Expenses), Expenses[payment_date] IN CALENDAR(mindt, maxdt)), "UniqueID", Expenses[item_id])))

var ranking = RANKX(items, currentvalue,,ASC)

RETURN DIVIDE(ranking, total)*100