cancel
Showing results for
Did you mean:
Regular Visitor

## Calculated item alternative

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Calculated item alternative

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.

11 REPLIES 11
Community Support Team

## Re: Calculated item alternative

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.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: Calculated item alternative

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?

Super User

## Re: Calculated item alternative

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.

Highlighted
Regular Visitor

## Re: Calculated item alternative

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

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

Super User

## Re: Calculated item alternative

Hi,

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

Regular Visitor

## Re: Calculated item alternative

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

Super User

## Re: Calculated item alternative

Hi,

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

Regular Visitor

## Re: Calculated item alternative

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

Super User

## Re: Calculated item alternative

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.