Count measure values

[Ohad]

Hi,

I have the following table:

Bill ID                    Item Name                         Quantity

101                         Coffee                                  1

101                         Salad                                    2

101                         Sandwich                             1

102                         Muffin                                  3

102                         Cookie                                 3

103                         Coffee                                  1

104                         Sandwich                             1

104                         Cookie                                 3

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,

Ohad

[v-shex-msft · Accepted solution]

Hi @Ohad,

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

[MFelix]

Hi @Ohad,

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

[Ohad]

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.

Thanks for your help,

Ohad