I need to calculate a Sex ratio ([male/female]*100) for every year.
I read on the forum to make 3 measure e.g F, M and use sexratio=M/F.
I am having problems calculating the measures F and M, which the numbers shown in the columns Female and male.
Original Excel input
In Power BI
Solved! Go to Solution.
You could create the measure below to calculate the Sex ratio ([male/female]*100) for every year without Unpivot Columns.
Sex_ratio = VAR male = SUM ( Sheet1[Male] ) VAR female = SUM ( Sheet1[Female] ) RETURN DIVIDE ( male, female ) * 100
You could refer to the output below.
Hope it can help you!
Hi Cherry, thanks for showing me this method of calculating the sex ratio before unpivoting the columns. I was not aware you could do it as you proposed.
Does this means that it is not possible to calculate the sex ratio if the tabel has been pivotted?
If you want to get your desired result on an unpivoted dataset, try these measures:
Males = CALCULATE(SUM(Data[Aantal]),Data[Sex]="Male")
Females = CALCULATE(SUM(Data[Aantal]),Data[Sex]="Female")
Sex ratio (%) = [Males]/[Females]
Hope this helps.
Ashish, on the left is is my data set in the data tab view, on the right I have choosen a matrix (left hand) adn some other visuals.
I cannot calculate the sex ratio measure in report tab based on the columns Female and male with the solution provided.
Sorry about the confusion, I hope now it is clear.