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...
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
Super User
7 years ago

Hi,

 

Try this measure for Golden Points

 

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

Hope this helps.

  • Kevin_Gitonga's avatar
    Kevin_Gitonga
    Helper I
    7 years ago

    Hi, Ashish_Mathur, Thanks for your assistance so far.  I tried this and it works well for the row total, however the total of the row total column(Bottom left) is not givign the right answer e.g here it gives 320 while in the real sense it should be 657 when you compute the totals.

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

    I'll need to see the PBI file.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super 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
    Helper I
    7 years ago
    Thanks, this worked well
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    You are welcome.

  • Kevin_Gitonga's avatar
    Kevin_Gitonga
    Helper 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
    Super 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.