cancel
Showing results for
Did you mean:

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

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
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
11 REPLIES 11
Resident Rockstar

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.

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!
Solution Sage

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

Solution Sage

Frequent Visitor

Thre you go

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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
Frequent Visitor

Thanks a lot Ashish. It works

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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
Super User

Hi,

Share some data and clearly show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Fabric Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors