Forum Discussion

maashi's avatar
maashi
Frequent Visitor
2 years ago
Solved

Multi level distinct counting

I'm trying to count some customer complaints with multiple filters, and am a bit stumped on how to write this measure.    Link to sample data: Freight snapshot.xlsx   I need to count some freigh...
"Need Help"
  • Anonymous's avatar
    Anonymous
    2 years ago

    1st created the sales order repeatation count

    Freight count with respect to sales order no is calculated

    Freight count and sales order repeatation number is compared

    Net sales order number is calculated

    If this solves your problem then accept the same as your solution

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi maashi 

 

First, you need to create a measure to calculate how many reasons one "sales no" has:

Reason count = 
VAR _no = SELECTEDVALUE(fact_NCP_Reporting[Sales Order No])

RETURN
CALCULATE(DISTINCTCOUNT(fact_NCP_Reporting[NCP Reason Categories.Reason Category]),FILTER(ALL(fact_NCP_Reporting),'fact_NCP_Reporting'[Sales Order No] = _no))

 

Then modify your original measure:

Freight Count = 
CALCULATE(
            DISTINCTCOUNT(
                fact_NCP_Reporting[Sales Order No]),
                FILTER(ALL('fact_NCP_Reporting'),
                'fact_NCP_Reporting'[NCP Reason Categories.Reason Category]="Freight"&&[Reason count]=1)
                )

 

 

Result:

 

 

 

 

 

 

Best Regards,

Jayleny

 

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