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.
