Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RonLieuw
Helper I
Helper I

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

11 REPLIES 11
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

HI , 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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 viewData set in Data tab viewMatrix table in Report viewMatrix table in Report view

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.