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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
singhranjeet101
Frequent Visitor

Count Values at the Firm Level not at office level

Hi Team, I have got a data set which includes three columns 

Clients Name | Offices | Revenue

I am looking to count the clients which have a revenue of more than 1M But when i try to count using the dataset i get total 42 clients not 52 because my measure counts clients at the office level. How i can create a measure in which it should count clients on the basis of Total Revenue by Clients not Total Revenue by offices. 

 

Also, when i need to apply filter at the firm level it should give me the clients by office level.

 

I can share the data set what i am trying to do in power BI. I have performed the function in excel and i want to do exactly the same in excel.

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

11 REPLIES 11
Mikelytics
Resident Rockstar
Resident Rockstar

Hi @singhranjeet101 ,

 

I do not have your dataset but based on your description it should be something like the following. I use a dataset from me and there I counted the customers with more than 3.000.000 Sales. The formula iterates through the product list and checks whether value is bigger then 3.000.000. If yes it gives 1 otherwise 0. After all products are iterated it takes the sum of the 1 and 0 values.

Iterator Function | COUNTX | Products > 3.000.000 = 
SUMX(
    VALUES(DimProduct[ProductName]),
    IF([SUM Sales] > 3000000,1,0)
)

 

see the result. Left the table with sales per product, on the right side the KPI Card with the count based of the formula above.

Mikelytics_0-1667328713047.png

 

Best regards

Michael

 

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

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!
mangaus1111
Solution Sage
Solution Sage

you can share a OneDrive link or copy past the table here from excel

mangaus1111
Solution Sage
Solution Sage

Hi @singhranjeet101 ,

please share your dataset

Thre you go

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish, Following up on the same scenario. I also want to sum the name which has a revenue of greater than 1M. I am able to do it on a firm's level but when apply a office filter i get wrong sum of revenue by office.

Below is the Dax im using.

 

Clients Rev >1M =
CALCULATE
    (
     SUM([Rev]),
    FILTER(
            Values(
                 'Data'[Name]),
                                                         'Data'[Rev] >= 1000000  
     
        )
             )
 
I was wonderinf if you could help?
 
Thanks

Thanks a lot Ashish. It works

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish, Following up on the same scenario. I also want to sum the name which has a revenue of greater than 1M. I am able to do it on a firm's level but when apply a office filter i get wrong sum of revenue by office.

Below is the Dax im using.

 

Clients Rev >1M =
CALCULATE
    (
     SUM([Rev]),
    FILTER(
            Values(
                 'Data'[Name]),
                                                         'Data'[Rev] >= 1000000  
     
        )
             )
 
I was wonderinf if you could help?
 
Thanks

Hi,

Share some data and clearly show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Who do i upload the excel? its not giving me an option?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.