Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Percentage as per excel sheet

Hi Guys,
I have to calculate percentage respective dimensions. we have "status" colunm and within we have "C"(Complience) and "N" (Non Complience) status. we need to calculate % if Complience status ='c' then it will give us Complience % respective Name dimension if Complience status ='N' then it will give us non Complience % respective Name dimension respective total of all status count. but what % we calculate(respective Complience/non Complience) result must be in one colunm.

this link for sample dataset :

https://docs.google.com/spreadsheets/d/1uoWeb_h2IKVuYwPVAQcPYsSHaCNa_hAM/edit?usp=share_link&ouid=109387920263136343535&rtpof=true&sd=true  

Dax I have used:
1)Status Count = CALCULATE(COUNT(SLA[status]))
2)Complience Count = CALCULATE(COUNT( SLA[status]),FILTER(SLA,SLA[status]="C"))
3)Non Complience Count = CALCULATE(COUNT( SLA[status]),FILTER(SLA,SLA[status]="N"))
4)test21 = [Complience Count]/[Status Count] 
5)test22 = [Non Complience Count]/[Status Count] 

6)Complience = Var a = SUMMARIZE(SLA,SLA[Airline_Name],SLA[Name],SLA[Stations],SLA[Business],SLA[Mode],"percentvalue",[test21])
return
AVERAGEX(a,[percentvalue])

I am able to calculate it separatily so thats why not getting correct output as i expected. let me know how we can calculate it dynamically repective all filters.

If getting any lead really Appricated.
Thanks.

 

No Replies