DAX help

[Anonymous]

why does this measure not work?

penetration = divide(CALCULATE(sum(Detail[Sales New Used]),Detail[Sales New Used]="New"),SUM('Dealer Summary'[Regs]))

sales new used needs to be divided by Regs but only the 'new' must be totaled and divided.

[v-jayw-msft]

Hi @Anonymous ,

Might because of the denominator. Try:

penetration = divide(CALCULATE(sum(Detail[Sales New Used]),Detail[Sales New Used]="New"),Calculate(SUM('Dealer Summary'[Regs]),All('Dealer Summary'))

 If it doesn't work, please show some sample data and expected result.

Best Regards,

Jay

[Anonymous]

This is the part of my measure which  doesnt work

Part 1 = Calculate(SUM('Detail'[Sales New Used]),('Detail'[Sales New Used]="New")
what i need this to do is filter all the New deals and count them up for each business area to then be divided by the RE
Below is all the data required for this measure :

Business area	REGS	Sales new used	penetration
A	200	New	(Count of new) ‘New’/REGS
B	300	New
C	453	New
A	200	Used
B	234	Used
C	754	New
A	768	New

[ryan_mayu]

@Anonymous 

is this what you want?

Column = 
CALCULATE(COUNT('Table'[Sales new used]),FILTER('Table','Table'[Business Area]=EARLIER('Table'[Business Area])&&'Table'[Sales new used]="New"))

[Anonymous]

this is what i want 

Business Area	REGS	Sales New used	Pen
A	968	2	2/968=0.002 x 100 = 0.2%
B	300	1
C	1207	2

[ryan_mayu]

@Anonymous 

pls see the attachment below

[Anonymous]

Why am i getting infinity and values over 100%

[ryan_mayu]

@Anonymous 

I suggest you check regs and count. You mean the business area have the results for regs and count, but result is infinity?

[Anonymous]

yes 

[ryan_mayu · Accepted solution]

@Anonymous 

because denominatordenominator is 0.

you can use DIVIDE

Result = DIVIDE([count],[Regs2])

[Anonymous]

@v-jayw-msft I have added a sample data table

[ryan_mayu]

@Anonymous 

maybe you can try to saparate the DAX to two parts and check which part didn't return the expected result.

CALCULATE(sum(Detail[Sales New Used]),Detail[Sales New Used]="New")

SUM('Dealer Summary'[Regs])