Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

% of a column

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?

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

       

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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.