How to show averages based on 3 columns

[Anonymous]

Hi all,

 Trying to show 2 measures in cards;

1. the likelihood an order will delay more than once [Card shows result like this "Orders delay on average x1.4 times"]
2. if delays then cancels, how many times does it delays on average before cancellation.

[Data table shown below here]

[AllisonKennedy]

@Anonymous  a bit more info on your data would help us here, but here's my best guess at what you're after. 

Try a measure:

DelayLikelihood=DIVIDE(COUNTROWS(FILTER(Sales, Sales[Type of Issue]="Delayed")), COUNTROWS(FILTER(Sales,ALL(Sales[Type of Issue])), 0)

DelayAvg= AVERAGEX(SalesOrderHeaders, [DelayLikelihood])

To get the text around it you can use a custom visual or concatanate or COMBINEVALUES with the above measure and the text you want:

Card Value = "Orders delay on average x" & DelayAvg & " times"

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[v-xuding-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved? Is the answer that AllisonKennedy helpful? If it is, kindly mark the helpful answer as solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from the thread.

[amitchandak]

@Anonymous , sales order more than one delay

sumx(filter(summarize(Table, Table[sales order number], "_cnt",countx(filter(Table,table[type of issue]="Delayed"),Table[sales order number])),[_cnt]>=1),[sales order number])

The second one can you explain more Avg of what you need

[Anonymous]

Fantasitic, will try that, thank you. 

For the 2nd card; if the sales order has both a 'delayed' and a 'cancelled' entry (as the data is record in a log fashion, each new change is entered in a new row as it comes in), on average how many 'delayed' rows are there for the sales order number before it's 'cancelled'