cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RonLieuw Regular Visitor
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

orig.png

Unpivotted file

table.png

 

In Power BI

t2.png

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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
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.

 

Capture6.PNG

Hope it can help you!

 

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.
RonLieuw Regular Visitor
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?

Highlighted
Super User
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.

RonLieuw Regular Visitor
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
Super User

Re: Calculated item alternative

Hi,

 

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

RonLieuw Regular Visitor
Regular Visitor

Re: Calculated item alternative

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

Super User
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.

RonLieuw Regular Visitor
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 tab.pngData set in Data tab viewReport tab.pngMatrix table in Report view

Super User
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.