Forum Discussion

AhmadBakr's avatar
AhmadBakr
Icon for Helper IV rankHelper IV
2 years ago

Using a ranked bar chart to filter the transactional table

I am buiding a dashboard for POs. Concept of the dashboard is as follows:

1- User has the ability to see the Amounts or count of orders
2- User has the ability to show topN or topN% (Pareto) which get applied to top buyers and top suppliers

 

1 and 2 are implemented via parameters, there is a parameter to select between Amount or Orders, another to select TopN or TopN%, and parameters for the TopN values (3 to 20), and TopN% values (from 10% to 100%)

 

Source data is one table "Open_PO" with records which have granularity of PO_Ref (unique Key).
Buyers or suppliers can be repeated. There is a column for Amount (of the PO), Matched Amount (Paid amount) and Open Amount (unpaid).

Measures definitions:   (focussing at the end on Buyers Pareto chart preparation)

ParameterOptions = // Selector parameter for Amount/Orders

DATATABLE(

"Name", STRING,
"Index", INTEGER,
{
{"Amount", 1},
{"Orders", 2}
}

)


SelPar = SELECTEDVALUE(ParameterOptions[Name])


Par =

SWITCH(
[SelPar],
"Amount", SUM(Open_PO[Amount SAR]),
"Orders", COUNTA(Open_PO[PO_Ref])
)
// Total amount of orders or total orders count


ParClr =
SWITCH(

[SelPar],
"Amount",
CALCULATE(

SUM(Open_PO[Matched Amount SAR]),
Open_PO[Matched Amount SAR] = Open_PO[Amount SAR]
),

"Orders",
CALCULATE(

COUNTA(Open_PO[PO_Ref]),
Open_PO[Matched Amount SAR] = Open_PO[Amount SAR]
)

)
// Sum of matched amounts of fully delivered orders, or count of fully delivered orders


ParOk =
SWITCH(

[SelPar],
"Amount",
CALCULATE(

SUM(Open_PO[Matched Amount SAR]),
Open_PO[Matched Amount SAR] <> Open_PO[Amount SAR]
),

"Orders",
CALCULATE(

COUNTA(Open_PO[PO_Ref]),
Open_PO[Matched Amount SAR] <> Open_PO[Amount SAR]
)

)
// Sum of amounts of partially delivered orders, or count of not fully delivered orders (i.e. remaining orders = unclear orders)
// For orders [ParRem] and [ParOk] will give same numbers


ParDlv = [Par] - [ParRem] // check comments below

// Delivered amounts or orders.
// For orders, delivered orders means (matched amount = total amount) for each order
// So for orders there are 2 states: delivered (cleared) orders, and remaining (unmatched) orders.
// For the Amount, matched amout per order may be = total amount ==> order is fully delivered
// Or matched amount <> total amount ==> order is partially delivered hence there is partial matching
// So for Amounts there are 3 states: Matched amounts:
// 1- Sum of amounts corresponding to matched deliveries. This may contain amounts corresponding to a full amount of an order (i.e. cleared order) or not (partial delivery)
// 2- Sum of amounts corresponding to unmatched deliveries. This is the remaining amounts
// 3- Sum of amounts corresponding to full order amount per order. This is the sum of amounts corresponding to full delivery (i.e. cleared orders)
// So for amounts, the remaining amount = full amount - matched (full .. matched = amount) - matched (partial .. matched <> amount)
// While for orders, the remaining orders = total no. orders - num of mating orders (full .. matched = amount)
// Hence the Delivered for amounts will be matched (full) + matched (partial), while delivered for orders will be count of orders with fully matching amounts
// [Par] reflects the full amount of orders or the total number of orders
// [ParOk] reflects partial matching, i.e. sum of matching amounts not equal to the order amount or the count of orders with amounts not fully matching the order amounts
// [ParClr] reflects cleared orders, i.e. sum of matching amounts equal to orders amounts, ot the count of orders fully matching
// For clarity, regarding amounts: [Par] = [ParOk] + [ParClr] + [ParRem], regarding Orders [Par] = [ParClr] + [ParRem] ([ParRem] and [ParOk] are the same, non-cleared orders)
// Thus I cannot use [ParOk] to represent the delivered because on the amounts side it reflects the partial delivery only, and on the orders side it reflects the open orders


ParRem =
IF(

[SelPar] = "Amount",
[Par] - [ParClr] - [ParOk],
[Par] - [ParClr]

)
// For amounts: the unmatched amount sum,
// For orders: the count of unmatched orders (which will be corresponding to the scope of [ParOk])
// For orders [ParRem] and [ParOk] will give same numbers


ParRemByrRank =
RANKX( // Rank Buyers by remaining amount/orders

ALLSELECTED(Open_PO[Buyer]),
[ParRem],
,
DESC,
Dense

)


ParRem%RemTotRunSumByr =
VAR CurrentRank = [ParRemByrRank]
VAR RunSum =

SUMX(

FILTER(

ALLSELECTED(Open_PO[Buyer]),
[ParRemByrRank] <= CurrentRank //Accumulate descendingly all remaining amount/orders less than the current buyer rank

),

[ParRem]
)

RETURN
DIVIDE( // calculate the %accumulated remaining out of grand remaining

RunSum,
[ParRemTot]

)


TopN% = {10,20,25,30,40,50,60,70,75,80,90,100}


TopN = GENERATESERIES(3, 20, 1)


TopN%Value = SELECTEDVALUE('TopN%'[TopN%], 80)


TopNValue = SELECTEDVALUE('TopN'[TopN], 5)


ParRemTopRnkByr =
SWITCH(

[SelRankMethod],
"TopN%",

IF(

[ParRem%RemTotRunSumByr] <= [TopN%Value]/100,

IF(

[ParRem] > 0,
[ParRem]

)

),

"TopN",

IF(

[ParRemByrRank] <= [TopNValue],

IF(

[ParRem] > 0,
[ParRem]

)

)

)


I have a bar chart showing TopN% or TopN buyers vs. the Amount/Orders count for each user.

When I show on a table visual Buyers and ParRemTopRnkByr, the table shows only buyers corresponding to the selected values (Amount/Orders, TopN/TopN% buyers)
But if I try to add PO_Ref from the Open_PO table, the TopN filter gets messed up and all records show, because it is of less granularity.

Hence I am not able to show the user a table containing the PO_Ref records of the TopN% or TopN buyers shown on the chart.