CODEWITHSUNDEEP
powerbi
Jay Kushwaha
Jay Kushwaha

Reputation: 1

count unique cities having total sales more than 50% in Power BI, DAX query

Here is my data sample, of Table "Sales"

City    Items   Target  Sales
ABC     Pen     100     20
ABC     Pencil  200     40
ABC     Glue    100     68
CDE     Copy    50      37
CDE     Books   70      20
CDE     Rubber  200     156

I want a DAX query for Power BI, to count unique cities having total sales more than 50% for a large set of records with multiple Cities / States / Items

Upvotes: 0

Views: 658

Answers (1)

Olly
Olly

Reputation: 7891

You need to iterate over the distinct values of the City field, evaluate the Sales % Target for each City, then count the number of values exceeding 50%.

Cities >50% = 
    COUNTX (
        VALUES ( Sales[City] ),
        IF ( 
            CALCULATE ( 
                DIVIDE ( 
                    SUM ( Sales[Sales] ),
                    SUM ( Sales[Target] )
                )
             ) > 0.5,
            1
        )
    )

Upvotes: 1

Related Questions