Aggregating Distinct Count measure and Aggregating IF measure

[Kevin_Gitonga]

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.

[v-jiascu-msft · Accepted solution]

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 · Accepted solution]

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]

You are welcome.

[Kevin_Gitonga]

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

Hi,

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