Forum Discussion

BhagyashriM42's avatar
BhagyashriM42
Frequent Visitor
4 years ago
Solved

Calculated column- percentage using different columns

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.

 

 

Need Help

9 Replies

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    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

  • BhagyashriM42's avatar
    BhagyashriM42
    Frequent Visitor
    4 years ago

    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's avatar
      SpartaBI
      Community Champion
      4 years ago

      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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    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's avatar
      BhagyashriM42
      Frequent Visitor
      4 years ago

      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