Adding consolidation to existing function

[Anonymous]

I am in desperate need of a solution that I have been trying to find for so long. I have a report evaluating how well a vendor does in getting our product to us on time and with a minimum condition of receiving 90% or more of the ordered product. However, I am trying to consolidate the data to only show occurrences that happen singularly based on the PO Number, PO Line Number, and Item number. Right now the data is counting each occurrence (i.e. PO # 1100566 has 4 receipts we want to count the sum of all and compare). I have measure created that sum the received quantity and also take the maximum receipt day. However, if I could get the function to be based on PO Number is should give me accurate information. 

Function: 

Test PO Fulfill = CALCULATE(IF(AND([QuantityPercent]>=.90,[MaxIAJDayDiff]<=-5),"Early",IF(AND([QuantityPercent]>=.90,AND([MaxIAJDayDiff]>=-4,[MaxIAJDayDiff]<=3)),"On-Time","Late")), ALLEXCEPT(OnTimeDelivery, OnTimeDelivery[PONumber], OnTimeDelivery[POLineNumber], OnTimeDelivery[ItemNumber]))

Visual: 

 So the numbers in the donut chart should match the table with 11 "Lates," 28 "On-Time," & 2 "Early." If there is a way to do this in the function above that would be the easiest method, and hopefully, there is a chance it can. Thank you for any help someone can provide!

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

Create a measure as below:

Measure = CALCULATE(COUNT('Table'[Test PO Fulfill]),FILTER(ALL('Table'),'Table'[Test PO Fulfill]=MAX('Table'[Test PO Fulfill])))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

I am not sure if I understand your question correctly ... you would like a donut chart which measures the percentage represented by each of the status (which is a metric calculated by the formula you provided)?

Technically it's not possible to add measures to the legend of the visual, so ..... one way to look at the problem is that you can create a table containing the status you want, and then create another measure that reflects the count of the status. The dimension table shall look something like this:

The measure looks like this (I didn't test the expression ... just to give you an idea of the DAX expression):

Count = VAR _tbl = SUMMARIZE(OnTimeDelivery,[PONumber],[POLineNumber,[ItemNumber],"PO Status",[Test PO Fulfill]])
        VAR _legend = FIRSTNONBLANK(Table,[Status])
        RETURN COUNTROWS(FILTER(_tbl,[PO Status]=_legend))

I hope you get my point.