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
MAAbdullah47
Helper V
Helper V

Distinct Count not working

Dear All, I visited the following post: http://community.powerbi.com/t5/Desktop/how-to-count-distinct-values-in-a-column/m-p/183518#M80566

 

When I create Measure the function Distinct Count working correct but If I create it on column it give wrong results , please advise

8 REPLIES 8
MarcelBeug
Community Champion
Community Champion

Based on the information you provided, it can be anything.

 

So my advice is to provide more information, like what formula do you use, what is the actual result, the expected result, why and any other specific information that increases the odds of you getting the help you are looking for.

Specializing in Power Query Formula Language (M)

Strange !!

@MAAbdullah47

 

This depends on your table structure. Calculated column will calculate on each row level, measure will aggregate on current slicing context. For your current context in your visual, if it contains multiple entries about "current slicing field-Order No", it will be calculated multiple times when using calculated column.

 

See my sample below:

 

9.PNG

 

As we have date dimension slicing, we have multiple entries for "User-Order". If you use calculated column, it will duplicate the result.

 

For better understanding, please refer to this blog: Calculated Columns and Measures in DAX

 

Regards,

 

 

But How I can solve this problem, The Fields That I have is:

 

Order No,

Branch Name  

Order Type (Pickup, Di-In, Delivery)  

Meal:  (Breakfast, Dinner, and Lunch) 

Total Order Price

Total Quantity 

 

I want to calculate the Average Order Size (Total Order Price/No Of Orders) 

In each branch for (Order Type and Meal),

I'm now doing the query (R Language) as the following:

 

Branch_Order_Size<-select(Order_Product_Sub,Order No,orders.quantity,orders.final_price,branches.name,Meal,Order_Type) %>%
group_by(Order No,branches.name,Meal,Order_Type) %>%
summarise (Total_Sales= sum(orders.products.final_price),Total_Quantity=sum(orders.products.quantity),Total_Branch_Orders=length(unique(Order No))) %>%
ungroup() %>%
as.data.frame()

 

The Right No Of Orders should be: 651895

If I calculated it as a measure It gives the right Number, If column it gives me: 1,689,975 

 

 

What change shall I do in the query to give me the right total number of orders?

 

 

@MAAbdullah47

 

Have you got solution for your requirenment? If you got the solution, Please let me know.

@MAAbdullah47 hi, did you get to a solution? I'd also like to know

If It is in Measure Result is: 651895 (Correct)

If it is in column Result is: 1689975 (Wrong)  

Here Is the formula: 

 

Number Of Orders In Branch = CALCULATE(DISTINCTCOUNT(Branch_Order_Size_1[Order No])) (Correct Result if it is inside Measure)

 

Same formula:

 

No Of Orders In Branch = CALCULATE(DISTINCTCOUNT(Branch_Order_Size_1[Order No])) (Wrong Result if it inside Column) ,

 

I need it based on a column cuz If it is a measure I want to divide it by the Total Sales:

 

Avg Price Sales = Branch_Order_Size_1[Total Sales]/sum([Number Of Orders In Branch]) ----> Not Work cuz it is measure not column

 

For this I need it to be in a column not measure, you got the point?

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.