Want to count the ID having measure calculation greater than 100%

[AshDil]

Hi,

I have data as follows:

ID	CATEGORY	SUB-CATEGORY	SALES	TARGET
1	A	AA	100	150
2	B	BB	200	200
3	C	CC	300	200
4	A	AB	400	300
5	B	BC	500	400
6	C	CA	100	150
7	A	CC	200	150
8	B	BB	300	300
9	C	AA	400	500

I need to count the ID having Divide(Sales,Target) > 100%, in above case the count is 4.

Please help me to do it.

Thanks,

AshDil.

[Syndicate_Admin]

Hello Dear,

If the data is the same as you uploaded it, you must occupy this DAX

Divide = 
COUNTROWS(
    FILTER('Table (3)',
    ( 'Table (3)'[Ventas] / 'Table (3)'[Blanco] ) > 1   )
)

[AlexisOlson]

How about counting rows where Sales > Target?

COUNTROWS ( FILTER ( Table1, Table1[Sales] > Table[Target] ) )

[AshDil]

Hi @AlexisOlson & @Syndicate_Admin ,

If Sales & Target present in different fact tables then how to do it. The both fact tables are connected to calendar table and common column between two fact tables is ID.

Please help me to do it.

Thanks,

AshDil.

[AlexisOlson · Accepted solution]

Maybe something like this?

SUMX (
    VALUES ( dimID[ID] ),
    IF (
        CALCULATE ( SUM ( Sales[Sales ) ) > CALCULATE ( SUM ( Target[Target] ) ),
        1
    )
)

It's hard to say exactly without seeing your model.