Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I find this forum a valuable resource and am hoping someone can assist me with the below problem.
I have a sample set of personal records, with some data and some n/a.
Name | Birthday | Nationality | Gender | Age | Occupation | Address |
Steve | 22/04/1990 | British | Male | 31 | Builder | Scotland |
Alice | n/a | Irish | Female | n/a | n/a | Ireland |
Bob | n/a | British | n/a | 52 | Teacher | n/a |
My goal is to show how complete these records are (as in, not showing n/a) in the form of a percentage. I have managed to achieve this with 2 columns. The first one counts the number of n/a and returns a percentage:
Count n/a Percentage =
DIVIDE (
COUNTX ( FILTER ( 'Table', 'Table'[Birthday] = "n/a" ), [Birthday] )
+ COUNTX ( FILTER ( 'Table', 'Table'[Nationality] = "n/a" ), [Nationality] )
+ COUNTX ( FILTER ( 'Table', 'Table'[Gender] = "n/a" ), [Gender] )
+ COUNTX ( FILTER ( 'Table', 'Table'[Age] = "n/a" ), [Age] )
+ COUNTX ( FILTER ( 'Table', 'Table'[Occupation] = "n/a" ), [Occupation] )
+ COUNTX ( FILTER ( 'Table', 'Table'[Address] = "n/a" ), [Address] ),
COUNTROWS ( 'Table' ) * 7
)
The second column calculates the reverse of this to show percentage complete
Percentage Complete = DIVIDE(100 - ('Table'[Count n/a Percentage]*100),100)
This works fine as seen below:
However, if I use Name as a slicer this percentage doesn’t change to show only the percentage complete in that row.
My ideal output would be this:
Any help would be greatly appreciated.
Thank you
Solved! Go to Solution.
@ZW , I think this correct as measure.
Count n/a Percentage =
DIVIDE (
COUNTX ( FILTER ( 'Table', 'Table'[Birthday] = "n/a" ), [Birthday] )
+ COUNTX ( FILTER ( 'Table', 'Table'[Nationality] = "n/a" ), [Nationality] )
+ COUNTX ( FILTER ( 'Table', 'Table'[Gender] = "n/a" ), [Gender] )
+ COUNTX ( FILTER ( 'Table', 'Table'[Age] = "n/a" ), [Age] )
+ COUNTX ( FILTER ( 'Table', 'Table'[Occupation] = "n/a" ), [Occupation] )
+ COUNTX ( FILTER ( 'Table', 'Table'[Address] = "n/a" ), [Address] ),
COUNTROWS ( 'Table' ) * 7
)
Not as column , as first row do not have any N/a and it 71 %
you can have column like
Count n/a Percentage =
DIVIDE (
if('Table'[Birthday] = "n/a" ,1,0 )
+if('Table'[Nationality] = "n/a" ,1,0 )
+if('Table'[Gender] = "n/a" ,1,0 )
+if('Table'[Age] = "n/a" ,1,0 )
+if('Table'[Occupation] = "n/a" ,1,0 )
+if('Table'[Address] = "n/a" ,1,0 ) ,
7
)
But my advice is use first one as a measure
Thank you, I didn't realise columns and measures behaved differently when it came to splicing.
The new measure works perfectly!
@ZW , I think this correct as measure.
Count n/a Percentage =
DIVIDE (
COUNTX ( FILTER ( 'Table', 'Table'[Birthday] = "n/a" ), [Birthday] )
+ COUNTX ( FILTER ( 'Table', 'Table'[Nationality] = "n/a" ), [Nationality] )
+ COUNTX ( FILTER ( 'Table', 'Table'[Gender] = "n/a" ), [Gender] )
+ COUNTX ( FILTER ( 'Table', 'Table'[Age] = "n/a" ), [Age] )
+ COUNTX ( FILTER ( 'Table', 'Table'[Occupation] = "n/a" ), [Occupation] )
+ COUNTX ( FILTER ( 'Table', 'Table'[Address] = "n/a" ), [Address] ),
COUNTROWS ( 'Table' ) * 7
)
Not as column , as first row do not have any N/a and it 71 %
you can have column like
Count n/a Percentage =
DIVIDE (
if('Table'[Birthday] = "n/a" ,1,0 )
+if('Table'[Nationality] = "n/a" ,1,0 )
+if('Table'[Gender] = "n/a" ,1,0 )
+if('Table'[Age] = "n/a" ,1,0 )
+if('Table'[Occupation] = "n/a" ,1,0 )
+if('Table'[Address] = "n/a" ,1,0 ) ,
7
)
But my advice is use first one as a measure