Calculated item alternative

[RonLieuw]

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

[v-piga-msft]

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

[RonLieuw]

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?

[Ashish_Mathur]

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.

[RonLieuw]

HI Ashish_Mathur, thanks for your input. This certainly helps!.

Can I conclude that you cannot caculate the sex ratio on a pivotted list?

[Ashish_Mathur]

Hi,

v-piga-msft has already shared that solution with you.

[RonLieuw]

Asish, v-piga-msft solution was for an unpivotted file with columns for male and female!

[Ashish_Mathur]

Hi,

In which case, i am not clear about the terminology you are using.  Please show the dataset and the expected result.

[RonLieuw]

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.

Data set in Data tab viewMatrix table in Report view

[Ashish_Mathur · Accepted 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.

[RonLieuw]

Ashish, you the Men. That's the solution I was looking for. Thanks

[Ashish_Mathur]

You are welcome.