Forum Discussion
TomSinAA
Helper IV
Helper IVdax help
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.
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.
1 Reply
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.