Card - count

[dkrishnan]

Hi All

Thanks in advance for the help.

I am trying to create cards that shows number of customers that is meeting Sales Target as well count that is not meeting the target.

See sample table below which detailed customer by product with the sales, target and the variance between them.

Customer	Product	Sales	Target	Variance
Customer A	PROD A	$        100	$          50	$          50
Customer B	PROD A	$        200	$        300	-$       100
Customer C	PROD A	$        350	$        320	$          30
Customer D	PROD A	$        500	$        400	$        100
Customer E	PROD A	$        200	$        350	-$       150
Customer F	PROD A	$        400	$        450	-$          50
Customer A	PROD B	$        100	$        150	-$          50
Customer B	PROD B	$        400	$        300	$        100
Customer C	PROD B	$        350	$        320	$          30
Customer D	PROD B	$        300	$        400	-$       100
Customer E	PROD B	$        360	$        350	$          10
Customer F	PROD B	$        500	$        450	$          50

Now:-

(a) I have one customer that is not meeting sales target overall

(b) I have 3 customers that is not meeting target for PROD A

(c) I have  customers that is not meeting target for PROD B

I have slicers of Customer and Product

How do i dynamically display cards that shows the count of customers that is not meeting target.

I tried creating additional tables which shows the distinctcount by customers only where variance is less than 0. This didn't work when i add product as part of my slicer. If i include product in the additional table, the count will be off.

is there a way to achieve this as i am in the midst of miragting from Oracle BDD. BDD has a feature that shows summarization bar which show dimension values that exceed a threshold.

Any help is much appreaciated.

regards

deven

[v-lili6-msft · Accepted solution]

hi, @dkrishnan

After my test, you could try this way:

measure = CALCULATE(SUM(Table1[Variance] ))

Result = 
VAR _table =
    SUMMARIZE ( Table1, Table1[Customer], "var", [measure] )
RETURN
    IF (
        HASONEVALUE ( Table1[Product] ),
        CALCULATE ( COUNTA ( Table1[Customer] ), FILTER ( Table1, [measure] < 0 ) ),
        CALCULATE ( COUNTAX ( FILTER ( _table, [var] < 0 ), [Customer] ) )
    )

Result:

here is pbix file, please try it.

Best Regards,

Lin

[dkrishnan]

Hi Lin

Thank you so much. it worled.

appreciate the help.

regards

deven