How to average counts, and how to weight the count based on multiple reasons.

[COIL-ibesmond]

Hello,

This is a two part question.

The first part is related to calculating an average count of canceled orders.  For this it could be average per month, average per week, average per quarter, average per year.  I don't need the calculation hardcoded for a specific period, but an example of how to do so would be helpful in case I decide to put it on a card.  My thoughts are to set a time frame and bring in a date field with either the year, year/quarter, year/month, year, year/week, and the calculation would perform correctly based on the level of aggregation.

Thoughts?

The second part of my question is I want to apply a weight to the reasons why someone may have canceled an order.  For instance, say I have 5 canceled ordered for the month.  The reason column is a concatenated field which I parsed out into multiple rows.  So in this example, a canceled order may consist of one row or multiple rows depending on the number of reasons.

My data is currently set up as fact table and a support table.

Here is an example or my original data:

Date	Canceled Order	Reason(s)
12/3/23	ORD9846	Ordered by accident
12/7/23	ORD9915	Ordered wrong color; Ordered wrong size
12/15/23	ORD9934	Expected arrival too late
12/20/23	ORD9948	Unhappy with shipping costs; Heard bad reviews about product; Product looks cheaply made
12/26/23	ORD9979	Customer changed mind; Order no longer needed

This is after I parse the data into rows:

Canceled Order	Reason(s)
ORD9846	Ordered by accident
ORD9915	Ordered wrong color
ORD9915	Ordered wrong size
ORD9934	Expected arrival too late
ORD9948	Unhappy with shipping costs
ORD9948	Heard bad reviews about product
ORD9948	Product looks cheaply made
ORD9979	Customer changed mind
ORD9979	Order no longer needed

This is my model, however I could merge the data back to make one table with multiple rows for each reason:

Basically I want the calculation to return a count of 5 orders, where if I filter the data by a reason, it would calculate 0.5 for each row on orders ORD9915 and ORD9979 and calculate 0.33 for each row on order ORD9948.  Currently my calculation shows a total of 9 orders.

Thanks.

[v-tangjie-msft]

Hi @COIL-ibesmond ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) Click "transform data" to go to the power query, delete the date column, and then split the Reason(s).

(3) We can create a date table.

Date = CALENDAR(MIN('Canceled Orders'[Date]),MAX('Canceled Orders'[Date])) 

(4) We can create measures. 

Measure = 
var _a=COUNTROWS(ALL('Reasons Canceled'))
var _b=COUNTROWS(FILTER(ALL('Reasons Canceled'),'Reasons Canceled'[Canceled Order]=MAX('Canceled Orders'[Canceled Order])))
RETURN DIVIDE(_b,_a)

Measure 2 = 
var _a= COUNTROWS(ALL('Reasons Canceled'))
var _b= COUNTROWS(ALLSELECTED('Canceled Orders'))
RETURN DIVIDE(_b,_a,0)

(5) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[COIL-ibesmond]

@v-tangjie-msft 

Thank you for the attempt, but the two measures do not capture what I am looking for.  Unfortunately I currently cannot upload pbix files, but I will provide you with some more information.

I decided to merge the two tables, however I might prefer not to, but I know that would complicate the measure.

I expanded the reasons.

I created three measures:

Here is my canvas with an explaination:

As you can see in the table on the top, the weighted count shows correctly, but when the context of the table changes, as seen in the bottom table, the calculation doesn't work.  Thanks.

[v-tangjie-msft]

Hi @COIL-ibesmond ,

We can create a measure.

Measure 3 = 
VAR _a =
    CALCULATE (
        COUNT ( 'Merge Canceled Orders'[Canceled Order] ),
        FILTER (
            ALL ( 'Merge Canceled Orders' ),
            [Canceled Order] = MAX ( 'Merge Canceled Orders'[Canceled Order] )
        )
    )
VAR _b =
    CALCULATE (
        DISTINCTCOUNT ( 'Merge Canceled Orders'[Canceled Order] ),
        FILTER (
            ALL ( 'Merge Canceled Orders' ),
            [Canceled Order] = MAX ( 'Merge Canceled Orders'[Canceled Order] )
        )
    )
RETURN
    DIVIDE ( _b, _a)

Best Regards,

Neeko Tang

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

[COIL-ibesmond]

@v-tangjie-msft Aplogize for my delayed response, but I tried to implement this, but the the count doesn't match up.  

Measure 3 should match the distinct count of 5 in the example.