Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
COIL-ibesmond
Helper I
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:

DateCanceled OrderReason(s)
12/3/23ORD9846Ordered by accident
12/7/23ORD9915Ordered wrong color; Ordered wrong size
12/15/23ORD9934Expected arrival too late
12/20/23ORD9948Unhappy with shipping costs; Heard bad reviews about product; Product looks cheaply made
12/26/23ORD9979Customer changed mind; Order no longer needed

 

This is after I parse the data into rows:

Canceled OrderReason(s)
ORD9846Ordered by accident
ORD9915Ordered wrong color
ORD9915Ordered wrong size
ORD9934Expected arrival too late
ORD9948Unhappy with shipping costs
ORD9948Heard bad reviews about product
ORD9948Product looks cheaply made
ORD9979Customer changed mind
ORD9979Order no longer needed

 

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

Screenshot 2024-02-21 161454.png

 

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
v-tangjie-msft
Community Support
Community Support

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

vtangjiemsft_1-1708668896475.png

(3) We can create a date table.

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

vtangjiemsft_0-1708668857363.png

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

vtangjiemsft_2-1708668967359.png

vtangjiemsft_3-1708668984086.pngvtangjiemsft_4-1708668994262.png

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. 

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

Screenshot 2024-02-26 100429.png

 

I expanded the reasons.

Screenshot 2024-02-26 100448.png

 

I created three measures:

 

Screenshot 2024-02-26 100318.png

Screenshot 2024-02-26 100330.png

Screenshot 2024-02-26 100343.png

Here is my canvas with an explaination:

Screenshot 2024-02-26 100248.png

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.

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)

vtangjiemsft_0-1709020556827.png

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. 

 

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

 

Screenshot 2024-03-08 120201.png

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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