How to convert general number into percentage for males and females using the Tornado 2.1.0 app.

[Anonymous]

Hi

I need help to convert the general number displayed in the funnel graph into percentage. When I tried to click on the measure values (Female - green colour) and convert it into percentage, the displayed value is not correct. For example, the actual number of female as shown in the order: 

Female participants: 11, 9, 3, 1, 0, 1

When I tried to convert them into percentage, for the top row, green bar (female) shld show  11/ (20+11) = 35% and the purple bar (male) shld be 20/(20+11) = 65%

Likewise for the second row, the green bar (female) shld be 9/(12+9) = 43% and the 2nd purple row (male) shld be 57%. It goes on for the rest.

[v-yingjl]

Hi @Anonymous ,

You can create measure like this:

Male = 
DIVIDE (
    CALCULATE (
        COUNT ( 'Table'[Gender] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Business]
                IN DISTINCT ( 'Table'[Business] )
                    && 'Table'[Gender] = "Male"
        )
    ),
    CALCULATE (
        COUNT ( 'Table'[Gender] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Business] IN DISTINCT ( 'Table'[Business] ) )
    )
)

Female = 
DIVIDE (
    CALCULATE (
        COUNT ( 'Table'[Gender] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Business]
                IN DISTINCT ( 'Table'[Business] )
                    && 'Table'[Gender] = "Female"
        )
    ),
    CALCULATE (
        COUNT ( 'Table'[Gender] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Business] IN DISTINCT ( 'Table'[Business] ) )
    )
)

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yingjl

THanks. It works. 

I got it right but when I tried to use FY slicer to filter by years, the funnel graph did not update accordingly. Previously, the funnel graph that only highlights the absolute number could be updated accordingly whenever I select different years. 

Is there any way for me to do something to the DAX formula so that whenever I select different year, the funnel graph should be updated accordingly.

[v-yingjl · Accepted solution]

Hi @Anonymous ,

Just add [year] column into the filter formula:

Male = 
DIVIDE (
    CALCULATE (
        COUNT ( 'Table'[Gender] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Business]
                IN DISTINCT ( 'Table'[Business] )
                    && 'Table'[Gender] = "Male"
                    && 'Table'[FY Year] IN DISTINCT ( 'Table'[FY Year] )
        )
    ),
    CALCULATE (
        COUNT ( 'Table'[Gender] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Business]
                IN DISTINCT ( 'Table'[Business] )
                    && 'Table'[FY Year] IN DISTINCT ( 'Table'[FY Year] )
        )
    )
)

Female = 
DIVIDE (
    CALCULATE (
        COUNT ( 'Table'[Gender] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Business]
                IN DISTINCT ( 'Table'[Business] )
                    && 'Table'[Gender] = "Female"
                    && 'Table'[FY Year] IN DISTINCT ( 'Table'[FY Year] )
        )
    ),
    CALCULATE (
        COUNT ( 'Table'[Gender] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Business]
                IN DISTINCT ( 'Table'[Business] )
                    && 'Table'[FY Year] IN DISTINCT ( 'Table'[FY Year] )
        )
    )
)

Please check the sample file in the below.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VijayP]

@Anonymous 

Just follow these steps and let me know if this is good!

[Anonymous]

Hi @VijayP 

I cannot see your animate gif as the size is so small and I could not see the word or data in the gif. May I request for larger size, pls? 

[PhilipTreacy]

Hi @Anonymous 

Please download this PBIX file with a solution

Create these 2 measures

% Female = DIVIDE( SELECTEDVALUE('Table'[Female]) , SELECTEDVALUE('Table'[Male]) + SELECTEDVALUE('Table'[Female]))

% Male = DIVIDE( SELECTEDVALUE('Table'[Male]) , SELECTEDVALUE('Table'[Male]) + SELECTEDVALUE('Table'[Female]))

Regards

Phil

[Anonymous]

@PhilipTreacy 

Thank you very much. But the thing about your raw data is not the same as mine. I understand where you ar coming from. In your raw data, you have already aggregated the total number of male and female participants for each particular business. In my case, I have individual business that will indicate that the participant is either male or female

I agree with your approach but before I obtain your suggested table, how do I need to sum up all male and female participants from particular business into total male and female for that particular business? I am open to your suggestion.

My mock table with selected columns

[VijayP]

@Anonymous 

I hvcreted additional columns in Power Query, you can usee the Female 1 and Male 1 columsn in that chart

if you can share the pbix wihtout sensitive data i can help you!

[amitchandak]

@Anonymous , I think in this case you have to create a % column and use that

Male % , divide([male], [male]+[female])

female % , divide([male], [male]+[female])

[Anonymous]

Hi thanks @VijayP @amitchandak 

But the problem is that how do I need to create % column

Here is the mock data here:

How do I need to create? Do I need to unpivot the gender into Male and Female columns?

[Ashish_Mathur]

Hi,

Create these 2 measures

Male = calculate(distinctcount(data[email]),data[gender] = "male")

Female = calculate(distinctcount(data[email]),data[gender] = "female")

Now use the measures shared by Amit.