cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

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

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.

4 REPLIES 4
Community Support

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.

Helper I

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.

Community Support

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.

Helper I

@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.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors