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

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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