% of a column

[Anonymous]

I'm trying to calculate the % of waste distribution type for every month year so for example in the above image, I want a measure to calculate the % of every month so it should be like for Jan 2021 the % of Closed loop recycling =( 7/(7+15+24))*100 and same for landfill and open loop recycling. I want to display the % of waste distribution in a line chart against the date to get a trend of how much is he % of waste distrbution for every type in every month and year.

How do I create a measure for this?

[Anonymous]

Hi @Anonymous ,

Please have a try.

closed loop recying_mea =
VAR _1 =
    CALCULATE (
        COUNT ( table[waste distribution type] ),
        FILTER (
            ALL ( table ),
            table[date] = SELECTEDVALUE ( table[date] )
                && table[waste distribution type] = "Closed loop recyling"
        )
    )
VAR _all =
    CALCULATE (
        COUNT ( table[waste distribution type] ),
        FILTER ( ALL ( table ), table[date] = SELECTEDVALUE ( table[date] ) )
    )
RETURN
    ( _1 / _all ) * 100

 Other calculations similar to this one.

Or you can use a column.

closed loop recying_col =
VAR _1 =
    CALCULATE (
        COUNT ( table[waste distribution type] ),
        FILTER (
            table,
            table[date] = EARLIER ( table[date] )
                && table[waste distribution type] = "Closed loop recyling"
        )
    )
VAR _all =
    CALCULATE (
        COUNT ( table[waste distribution type] ),
        FILTER ( table, table[date] = EARLIER ( table[date] ) )
    )
RETURN
    ( _1 / _all ) * 100

Or you want the following output:

2021/1/1	closed...	7	(7/46)*100
2021/1/1	land...	15	(15/46)*100
2021/1/1	open...	24	24/46)*100

Have a try.

diff_mea =
VAR _1 =
    CALCULATE (
        COUNT ( table[waste distribution type] ),
        FILTER (
            all(table),
            table[date] = selectedvalue ( table[date] )
                && table[waste distribution type] = selectedvalue(table[waste distribution type])
        )
    )
VAR _all =
    CALCULATE (
        COUNT ( table[waste distribution type] ),
        FILTER ( all(table), table[date] = selectedvalue ( table[date] ) )
    )
RETURN
    ( _1 / _all ) * 100

diff_col =
VAR _1 =
    CALCULATE (
        COUNT ( table[waste distribution type] ),
        FILTER (
            (table),
            table[date] = EARLIER ( table[date] )
                && table[waste distribution type] = earlier(table[waste distribution type])
        )
    )
VAR _all =
    CALCULATE (
        COUNT ( table[waste distribution type] ),
        FILTER ( (table), table[date] = earlier ( table[date] ) )
    )
RETURN
    ( _1 / _all ) * 100

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

With the above formula it's showing an error for && waste data(3) waste distribution type portion. Can you help with this?

[Anonymous]

Hi @Anonymous ,

Please have a try.

diff_m =
diff_mea
    =
    VAR _1 =
        CALCULATE (
            COUNT ( table[waste distribution type] ),
            FILTER ( ALL ( table ), table[date] = SELECTEDVALUE ( table[date] ) ),
            FILTER (
                ALL ( wasteTABLE ),
                table[waste distribution type] = SELECTEDVALUE ( table[waste distributiontype] )
            )
        )
    VAR _all =
        CALCULATE (
            COUNT ( table[waste distribution type] ),
            FILTER ( ALL ( table ), table[date] = SELECTEDVALUE ( table[date] ) )
        )
    RETURN
        ( _1 / _all ) * 100

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.