cancel
Showing results 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

Frequent Visitor

## Count measure values

Hi,

I have the following table:

Bill ID                    Item Name                         Quantity

101                         Coffee                                  1

101                         Sandwich                             1

102                         Muffin                                  3

103                         Coffee                                  1

104                         Sandwich                             1

I would like to get the two following two tables:

Bill ID                    Quantity

101                         4

102                         6

103                         1

104                         4

Quantity              Bill Count

1                              1

4                              2

6                              1

What is the best way to do it?

Thanks,

1 ACCEPTED SOLUTION
Community Support

You can simply use summarize function to achieve your requirement:

`Total Quantity = SUMMARIZE('111','111'[Bill ID],"Quantity",SUM('111'[Quantity]))Quantity Count = SUMMARIZE('Total Quantity','Total Quantity'[Quantity],"Bill Count",COUNT('Total Quantity'[Bill ID]))`

In addition, if you not want the "Total Quantity" table, you can direct use below formula to get the quantity count.

```Table 3 =
Var temp= SUMMARIZE('111','111'[Bill ID],"Quantity",SUM('111'[Quantity]))
return
SUMMARIZE(temp,[Quantity],"Bill Count",COUNTX(FILTER(temp,[Quantity]=EARLIER([Quantity])),[Bill ID]))```

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
3 REPLIES 3
Community Support

You can simply use summarize function to achieve your requirement:

`Total Quantity = SUMMARIZE('111','111'[Bill ID],"Quantity",SUM('111'[Quantity]))Quantity Count = SUMMARIZE('Total Quantity','Total Quantity'[Quantity],"Bill Count",COUNT('Total Quantity'[Bill ID]))`

In addition, if you not want the "Total Quantity" table, you can direct use below formula to get the quantity count.

```Table 3 =
Var temp= SUMMARIZE('111','111'[Bill ID],"Quantity",SUM('111'[Quantity]))
return
SUMMARIZE(temp,[Quantity],"Bill Count",COUNTX(FILTER(temp,[Quantity]=EARLIER([Quantity])),[Bill ID]))```

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Super User

For the first result insert a matrix.or table.and add your BillID column and quantity and select the sum summarize option to the.quantity.

For the last result add Quantity and BillID for quantity select Don't Summarizeand for billid select coint distinct.

This.shuld.give you the expected results.
Regards
MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Frequent Visitor

Hi @MFelix

The solution is not working because in the first result table the quntity values (second column) are sum of quantity for each bill ID and in the second resault table the quatinty values (first column) are distinct values of quantity and it's not the same.

Buttom line, the second resault table should contain distinct values of the sum of quantity from the first result table and only then to count the bills.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

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

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors