Calculated column- percentage using different columns

[BhagyashriM42]

Hello Community,

I want to calculate a calculated column where it will give the % of particular categories as given in the screenshot,I have tried using earlier function to get count of "Happy" and as well as others i.e "Trust","Awareness","Diversity" (I need to calculate for only 3 KPI's) but that calculated columns are not working to get % for final calculated column.File attached for the reference.Thank you in advance for your reference.

This output expected:

Sample Data:

Thank you in advance for your reference.

[Ashish_Mathur]

Hi,

You should be writing a measure for this - not a calculated column formula.  If you are interested, share the link from where i can download your PBI file.

[BhagyashriM42]

Hi Ashish,

Right, I had done this on measure ,but there is a situation where I need a calculated column itself to take a lookup in other table and all other calculations are indirectly dependent.

Thanks,

Bhagyashri

[SpartaBI]

@BhagyashriM42 you can PM to do a zoom call

[BhagyashriM42]

Hello @SpartaBI ,

I have tried with below calculated columns,but with no luck (basically total of happy should only have divided total of awareness,total of trust,total of diversity other than these KPI should be blank)

Happy_tag =

if('Table'[KPI]="Happy","Happy",BLANK())

Other_tags =

if('Table'[KPI]="Trust","Trust",
if('Table'[KPI]="Awareness","Awareness",
if('Table'[KPI]="Diversity","Diversity",BLANK())))

Final_calcol =
VAR _current_category = 'Table'[Category]
VAR _current_agegroup = 'Table'[Age-Group]
VAR _condition_1 = 'Table'[Happy_tag]
VAR _condition_2 = 'Table'[Other_tags]

VAR _num = COUNTROWS(
FILTER('Table',
'Table'[Category] = _current_category
&& 'Table'[Age-Group] = _current_agegroup
&& 'Table'[KPI]= _condition_2

)
)

VAR _denum =
COUNTROWS(
FILTER('Table',
'Table'[Category] = _current_category
&& 'Table'[Age-Group] = _current_agegroup
&& 'Table'[KPI]= _condition_1

)
)

VAR _result = DIVIDE(_num, _denum)
RETURN
_result

Thank you so much for your quick response.

[SpartaBI]

Hey @BhagyashriM42, already very late my timezone. PM me tomorrow as early your timezone you can and we could do a quick zoom and look together

[BhagyashriM42]

Hi @SpartaBI ,

Let me know when to connect.

Thanks,

Bhagyashri

[SpartaBI]

 I'm free now

[SpartaBI · Accepted solution]

@BhagyashriM42 here is a link to the file with the solution:
Test-calcolfile.pbix

      

Showcase Report – Contoso By SpartaBI

[SpartaBI]

@BhagyashriM42 if I understood what you need then creare this calculated column:

Pct from Category and Age-Group = 
VAR _current_category = 'Table'[Category]
VAR _current_age_group = 'Table'[Age-Group]
VAR _current_kpi = 'Table'[KPI]
VAR _num = 
	COUNTROWS(
		FILTER('Table', 
		'Table'[Category] = _current_category 
			&& 'Table'[Age-Group] = _current_age_group 
				&& 'Table'[KPI] = _current_kpi
		)
	)
VAR _denum = 
	COUNTROWS(
		FILTER('Table', 
		'Table'[Category] = _current_category 
			&& 'Table'[Age-Group] = _current_age_group
		)
	)
VAR _result = DIVIDE(_num, _denum)
RETURN
	_result

      

Showcase Report – Contoso By SpartaBI