Forum Discussion

RonLieuw's avatar
RonLieuw
Helper I
8 years ago
Solved

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

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    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

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    8 years ago

    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's avatar
      RonLieuw
      Helper I
      8 years ago

      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's avatar
        Ashish_Mathur
        Super User
        8 years ago

        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.