Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Unpivotted file
In Power BI
Solved! Go to Solution.
Hi,
Do not drag Sex to the column area section. Just drag years to the row area section and then write the 3 measures that i shared with you in my earlier post.
Hi @RonLieuw,
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!
Best Regards,
Cherry
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?
Hi,
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.
HI Ashish_Mathur, thanks for your input. This certainly helps!.
Can I conclude that you cannot caculate the sex ratio on a pivotted list?
Hi,
v-piga-msft has already shared that solution with you.
Asish, v-piga-msft solution was for an unpivotted file with columns for male and female!
Hi,
In which case, i am not clear about the terminology you are using. Please show the dataset and the expected result.
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.
Hi,
Do not drag Sex to the column area section. Just drag years to the row area section and then write the 3 measures that i shared with you in my earlier post.
Ashish, you the Men. That's the solution I was looking for. Thanks
You are welcome.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |