dax help

[TomSinAA]

I have data in this format:

Category	Area	Requirement
Cat1	R1	1
Cat1	R1
Cat1	R2
Cat1	R3	2
Cat1	R4	3
Cat1	R5

I need to display the count and % of areas with and without requirements.  So that is looks like this:

Area	Count of Areas with no requirement	%	Count of Areas with requirement		Total	%
Cat1	2	40%	3	60%	5	100

Not sure how to approach this. 

[Anonymous · Accepted solution]

Hi @TomSinAA 

You can refer to the following measure

Count of Areas with no requirement = var a=SELECTCOLUMNS(FILTER('Table',[Requirement]<>BLANK()),"a",[Area])
return CALCULATE(DISTINCTCOUNT('Table'[Area]),'Table'[Area] in a=FALSE())

Count of Areas with  requirement = DISTINCTCOUNT('Table'[Area])-[Count of Areas with no requirement]

Areas with no requiremen t% = DIVIDE([Count of Areas with no requirement],CALCULATE(DISTINCTCOUNT('Table'[Area])))

Areas with  requiremen t% = DIVIDE([Count of Areas with  requirement],CALCULATE(DISTINCTCOUNT('Table'[Area])))

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.