Forum Discussion

Kevin_Gitonga's avatar
Kevin_Gitonga
Helper I
7 years ago
Solved

Aggregating Distinct Count measure and Aggregating IF measure

I have the following matrix table with the columns being products and the rows being the customer codes. What I want is to find the billings which is the distinct count of orders.I used the following for billing

Billing = DISTINCTCOUNT(Sales[InvNumber])

From that I am supposed to find golden points which is where if the billing(distinct count of orders) is more than 1 then Golden is one otherwise the golden point is 0.I used the following

Golden Point = if([Billing]>=1,1,0)

The problem is both these two measures do not show the row totals accurately as shown below.Total billing is not adding up and the Golden points is showing 1 for all in the totals column

 

Kindly help me on how I can make the row totals to be accurate.

General Comment
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Kevin_Gitonga,

     

    Try these two measures, please.

     

    Measure =
SUMX (
    SUMMARIZE (
        'table',
        'table'[ProductCode],
        'table'[CustomerCode],
        "Billing", DISTINCTCOUNT ( 'table'[InvNumber] )
    ),
    [Billing]
)

    Total Golden Point =
SUMX (
    SUMMARIZE (
        'table',
        'table'[ProductCode],
        'table'[CustomerCode],
        "Golden Point", IF ( DISTINCTCOUNT ( 'table'[InvNumber] ) >= 1, 1, 0 )
    ),
    [Billing]
)

     

     

     

    Best Regards,

  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    Try this measure

     

    Billing = if(HASONEVALUE(Data[ProductCode]),DISTINCTCOUNT(Data[InvNumber]),SUMX(SUMMARIZE(VALUES(Data[ProductCode]),[ProductCode],"ABCD",DISTINCTCOUNT(Data[InvNumber])),[ABCD]))

    Hope this helps.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

    The Golden Points measure can be revised to:

    Golden Point = SUMX(SUMMARIZE(GENERATE(VALUES(Sales[CustomerCode]),VALUES(Sales[ProductCode])),[CustomerCode],[ProductCode],"ABCD",if(DISTINCTCOUNT(Sales[InvNumber])>=1,1,0)),[ABCD])

    Hope this helps.

23 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Regarding the Golden Point columns:

    For your final "Golden Point" column on the right, I have a suspicion it's related to the "Total Billing" column, so that if the value in the "Total Billing" column is >=1, then the "Golden Point" column value will be 1, based on this snippet you posted below:

    Golden Point = if([Billing]>=1,1,0)

     
    It sounds like you want that to be "Total Golden Points" instead.  If that's correct then you'll want to re-write the code for that column so that it checks each of those 4 "Golden Point" columns and adds +1 to the "Total Golden Points" column when a golden point has been awarded for client/product match up.

    Regarding the "Total Billing" column:

    Do you have other code or calculations specific to that you could share with us?  I eyeballed your data but couldn't figure out how the numbers in the "Total Billing" column were being calculated.

    • Kevin_Gitonga's avatar
      Kevin_Gitonga
      Helper I
      7 years ago

      Yes I want the Total Golden Points, how do I go about wrirng a formula that would add the values per column to find the total golden points??. The logic behind billing is finding the number of disctint Invoices for a customer that contain a partilucar product used as the column in the matrix table. Currently the total billing is also not giving the correct sum of the values in the columns after using distinct count.Here is the sample data. I'd like the row totals to give the correct totals for the row

      TxDateCustomerCodeInvNumberProductCodeQty
      3/1/2019TUS56-NBINBI_INV227866MC8004
      3/1/2019TUS56-NBINBI_INV227866MC3025
      3/1/2019TUS56-NBINBI_INV227866MC7505
      3/1/2019TUS56-NBINBI_INV227866MC4515
      3/1/2019TUS56-NBINBI_INV227866MC4005
      3/1/2019TUS56-NBINBI_INV227866MC3032
      3/1/2019TUS56-NBINBI_INV227866MC6502
      3/1/2019TUS56-NBINBI_INV227866MC4014
      3/1/2019TUS56-NBINBI_INV227866MC7512
      3/1/2019TUS56-NBINBI_INV227866MC1522
      3/1/2019TUS56-NBINBI_INV227866MC30124
      3/1/2019TUS56-NBINBI_INV227866MC50024
      3/1/2019TUS56-NBINBI_INV227866MC45024
      3/1/2019TUS56-NBINBI_INV227866MC5523
      3/1/2019TUS56-NBINBI_INV227866MC5512
      3/1/2019TUS56-NBINBI_INV227866ME10154
      3/1/2019TUS56-NBINBI_INV227866ME1005
      3/1/2019TUS56-NBINBI_INV227866MB1030
      3/1/2019TUS56-NBINBI_INV227866MB10140
      3/1/2019TUS56-NBINBI_INV227866MC7002
      3/1/2019TUS56-NBINBI_INV227866MC15024
      3/1/2019QUI03-NBINBI_INV227845ME101162
      3/1/2019QUI03-NBINBI_INV227845MD2033
      3/1/2019QUI03-NBINBI_INV227845MD2024
      3/1/2019QUI03-NBINBI_INV227845MD2014
      3/1/2019QUI03-NBINBI_INV227845MD2001
      3/1/2019QUI03-NBINBI_INV227845MC5502
      3/1/2019QUI03-NBINBI_INV227845ME1003
      3/1/2019QUI03-NBINBI_INV227845MC1501
      3/1/2019QUI03-NBINBI_INV227845MC1001
      3/1/2019QUI03-NBINBI_INV227845MC60015
      3/1/2019QUI03-NBINBI_INV227845FN0020
      3/1/2019FAS01-NBINBI_INV227846FK0206
      3/1/2019FAS01-NBINBI_INV227846FK0216
      3/1/2019FAS01-NBINBI_INV227846FT0055
      3/1/2019FAS01-NBINBI_INV227846UB01120
      3/1/2019FAS01-NBINBI_INV227846UB0326
      3/1/2019FAS01-NBINBI_INV227846UB3821
      3/1/2019FAS01-NBINBI_INV227846UB3831
      3/1/2019WA04-NBINBI_INV227847FT0055
      3/1/2019WA04-NBINBI_INV227847EVE1161
      3/1/2019WA04-NBINBI_INV227847EVE1131
      3/1/2019QUI03-NBINBI_INV227850FT0055
      3/1/2019QUI03-NBINBI_INV227850FT0010
      3/1/2019QUI03-NBINBI_INV227850KEL14112
      3/1/2019QUI03-NBINBI_INV227850KEL12116
      3/1/2019QUI03-NBINBI_INV227850KEL14016
      3/1/2019QUI03-NBINBI_INV227850KEL12010
      3/1/2019QUI03-NBINBI_INV227850UB01024
      3/1/2019QUI03-NBINBI_INV227850MB1030
      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        7 years ago

        Hi,

         

        Try this measure

         

        Billing = if(HASONEVALUE(Data[ProductCode]),DISTINCTCOUNT(Data[InvNumber]),SUMX(SUMMARIZE(VALUES(Data[ProductCode]),[ProductCode],"ABCD",DISTINCTCOUNT(Data[InvNumber])),[ABCD]))

        Hope this helps.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    If in the Grand total column of Total billing, you want to see the summation of the numbers in the Billing column, that should be easy to do.  However, i think that result would be wrong.  The figues as they appear now seem correct to me.  This is simply because the same Customer may have bought multiple products in the same invoice.  So the Grand Total column for Billing should not be a straight summation.

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi Kevin_Gitonga,

     

    Try these two measures, please.

     

    Measure =
SUMX (
    SUMMARIZE (
        'table',
        'table'[ProductCode],
        'table'[CustomerCode],
        "Billing", DISTINCTCOUNT ( 'table'[InvNumber] )
    ),
    [Billing]
)

    Total Golden Point =
SUMX (
    SUMMARIZE (
        'table',
        'table'[ProductCode],
        'table'[CustomerCode],
        "Golden Point", IF ( DISTINCTCOUNT ( 'table'[InvNumber] ) >= 1, 1, 0 )
    ),
    [Billing]
)

     

     

     

    Best Regards,

    • Kevin_Gitonga's avatar
      Kevin_Gitonga
      Helper I
      7 years ago

      v-jiascu-msftThanks, these work as a solution to the problem I was having and well for that matter, the problem is that these measures take ages to calculate for my data which is alot of transactions spread over the last 6 months hence slow load times for my matrix table visual. Is there a better approach to reduce the perfomance hit since it seems it is iterating alot?