Forum Discussion

Kevin_Gitonga's avatar
Kevin_Gitonga
Icon for Helper I rankHelper 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...
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.

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 years ago

Hi,

I'll need to see the PBI file.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    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.

  • Kevin_Gitonga's avatar
    Kevin_Gitonga
    Icon for Helper I rankHelper I
    7 years ago
    Thanks, this worked well
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    You are welcome.

  • Kevin_Gitonga's avatar
    Kevin_Gitonga
    Icon for Helper I rankHelper I
    6 years ago

    Ashish_MathurI have a query on this, am I able to filter the products used in this formula using the values in another table.

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

     The products to filter in the formula are based on this table where different periods have diffrent products used in the Golden point formula above.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    Hi,

    I am not clear with your question.  Please share a simple dataset, explain the business context and show the expected result.