Forum Discussion

Ohad's avatar
Ohad
Frequent Visitor
9 years ago
Solved

Count measure values

Hi,   I have the following table:   Bill ID                    Item Name                         Quantity 101                         Coffee                                  1 101              ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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's avatar
MFelix
Super User
9 years ago
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's avatar
    Ohad
    Frequent Visitor
    9 years ago

    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