Calculating a KPI where unique order ID's, when identical, are considered a count of 1

[johnsimpson]

We are a Fulfillment business.  I have Sales Data, and I'm having trouble making some determinations within PowerPivot.

I am a novice user of PowerBI. Right now, I have a data set from Netsuite, it is raw data row-by-row and not grouped in anyway.  4 rows of data could contain values associated to the same order.  For example:

Order ID	Date	SKU	Sales Rep	Value	Demand Data Captured?
SO1234	9/1/2022	SKU123	Rep A	$500	0
SO1234	9/1/2022	SKU124	Rep A	$200	0
SO1234	9/1/2022	SKU134	Rep A	$300	1
SO1234	9/1/2022	SKU155	Rep A	$400	0
SO1244	9/1/2022	SKU156	Rep B	$500	0
SO1244	9/1/2022	SKU1656	Rep B	$200	0
SO1244	9/1/2022	SKU987	Rep B	$300	1
SO1244	9/1/2022	SKU193	Rep B	$400	1
SO1244	9/1/2022	SKU144	Rep B	$200	0

I want to create a calculation where, if an order has any "1" in the demand column - it is counted as one (even if multiple lines of an order have more than one 1, as is the case with SO1244) and the total is then divided by the total count of orders. This would mean I'd need to somehow have SO1244, although 5 lines of product, counted as one order instead of 5.

I hope this makes sense. I think I might need to do something with hierarchies first?

Ultimately, then I can say:

In September, Rep A entered 100 orders, and of those, x% had demand data captured.  In other words, without grouping by order#, I can certainly already say that % of lines/products had demand data (lines/SKU with demand = 1 divided by total lines) but this is an unhelpful statistic.

Any tips appreciated!

John

[Anonymous · Accepted solution]

Hi  @johnsimpson ,

Here are the steps you can follow：

1. In Power Query -- Add Column – Index Column – From 1.

2. Create calculated column.

Flag =
var _contain1=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Order ID]=EARLIER('Table'[Order ID])),"1",[Demand Data Captured?])
var _min=MINX(FILTER(ALL('Table'),'Table'[Order ID]=EARLIER('Table'[Order ID])),[Index])
return
IF(
   1 in _contain1  && 'Table'[Index]=_min,1,
   IF(
       NOT(1) in _contain1,1,0)
)

KPI =
var _group=SUMX(FILTER(ALL('Table'),'Table'[Order ID]=EARLIER('Table'[Order ID])),[Flag])
var _allgroup=SUMX(ALL('Table'),[Flag])
return
divide(_group,_allgroup)

3. Result:

Best Regards,

Liu Yang

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