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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Average of a count entries without group by

Hello. I have a some columns in my file that correspond to customer's orders: order date, payment date, Number of order, product typology, etc. The behavior of the file is: when some customer order more than 1 item, it creates two lines for the same order, for example:

 

 

Num orderOrderdateSkuarticle typologyCount of Num articlesMono or multi orders
12345610/12/2019654335322Multi
12345610/12/2019659905972Multi
2345610/12/2019659905971Mono

 

The 2 last columns were calculations done by me: if a order number appears more than 1 time, it is an order that have more than 1 article. 

 

Now I want to know the average of article's per multi orders but I don't want to do a group by (by Number of order) to perform this. The way I know is to perform a query that groups by Num of order (all lines of the same order are consolidated in only one) and then do the average. 

 

How to do? 

I tried "average_num_articles:=CALCULATE(AVERAGE(Table[Count of Num articles]);FILTER(Table;VALUES(Table[NumOrder])))" without success. 

 

Thank you.

5 REPLIES 5
AntrikshSharma
Community Champion
Community Champion

@Anonymous  Let me know if I wasn't able to understand your request:

1.PNG2.PNG3.PNG

Average of Articles = 
VAR CurrentOrder = jmsm[Num order]
VAR MultiOrders =
    FILTER (
        jmsm,
        jmsm[Mono or Multi Orders] = "Multi"
            && jmsm[Num order] = CurrentOrder
    )
VAR Result =
    AVERAGEX ( MultiOrders, jmsm[article typology] )
RETURN
    Result

 

PBI file attached below my signature:

Anonymous
Not applicable

@AntrikshSharma The average is on "count of num of articles", not articles typology. 

 

It didn't work when I tried in a cell (dax formula): The OrderNum cannot be determinated. "This happens when a measure formula refers to a columns that contains many values ..."

 

I think is related to the first VAR (currentorder). When I tried to write Table[NumOrder] it didn't recognize it (only columns with calculations).

@Anonymous Is this what you want?

1.PNG

Anonymous
Not applicable

Yes, but in a measure not in a column because the average is by OrderNumber and not by each line of each order number. 

@Anonymous Most of the code is same for Measure:

1.PNG

Measure = 
VAR CurrentOrder = SELECTEDVALUE ( jmsm[Num order] )
VAR MultiOrders =
    FILTER (
        ALL ( jmsm ),
        jmsm[Mono or Multi Orders] = "Multi"
            && jmsm[Num order] = CurrentOrder
    )
VAR Result =
    AVERAGEX ( MultiOrders, jmsm[Count of num of articles] )
RETURN
    Result

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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