Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only

View My Drafts

  • 1.  Calculate the percentage of a column based on one of the values in the column

    Posted 16 days ago

    This is a very odd request.  It is difficult to explain in words, so let me show you what I am trying to do.  Lets say I have a table with the following values.

    Category    Sales Qty    Current Year % Of Sales Compared to Helmets
    Helmets      150                             150/150 = 100%
    Gloves         70                                70/150 = 47%
    Pants           80                                80/150 = 53%

    I have tried doing this but it only works for the first row.
    CY% =

        VAR _myPercent =
            CALCULATE(
                SUM(Production[Value]),
                FILTER(ALL(Production), Production[Category] = "Helmets"),
                FILTER(Production, Production[Week] <= Max(DatesTable[CurrentWeek])),
                FILTER(Production, Production[Year] = Max(DatesTable[CurrentYear]))
            )  
        Return
       
            CALCULATE(
                SUM(Production[Value]),
                FILTER(Production, Production[Week] <= Max(DatesTable[CurrentWeek])),
                FILTER(Production, Production[Year] = Max(DatesTable[CurrentYear]))
            )  
            /

            myPercent



    ------------------------------
    Mike McLean
    ------------------------------


  • 2.  RE: Calculate the percentage of a column based on one of the values in the column

    Posted 16 days ago
    Veja se ajuda

    CY% = 
DIVIDE (
    SUM ( Tabela[Sales Qty] ),
    CALCULATE ( SUM ( Tabela[Sales Qty] ), Tabela[Category] = "Helmets" )
)​




    ------------------------------
    Vilmar Santos
    ------------------------------

    Original Message


  • 3.  RE: Calculate the percentage of a column based on one of the values in the column

    Posted 16 days ago

    When I try that it still only works for the first column.  Here is what I used based on your suggestion:

    CY% =
    DIVIDE(
        CALCULATE(
            SUM(Production[Value]),
            FILTER(Production, Production[Week] <= Max(DatesTable[CurrentWeek])),
            FILTER(Production, Production[Year] = Max(DatesTable[CurrentYear]))
        ),
        CALCULATE(
            SUM(Production[Value]),
            FILTER(Production, Production[Category] = "Helmets"),
            FILTER(Production, Production[Week] <= Max(DatesTable[CurrentWeek])),
            FILTER(Production, Production[Year] = Max(DatesTable[CurrentYear]))
        )
        ,0
    )


    ------------------------------
    Mike McLean
    ------------------------------

    Original Message


  • 4.  RE: Calculate the percentage of a column based on one of the values in the column

    Posted 15 days ago
    Try not to use multiple FILTER. If it doesn't work, attach your pbix please, it can be with dummy datas


    CY% =
DIVIDE (
    SUM ( Production[Value] ),
    CALCULATE (
        SUM ( Production[Value] ),
        FILTER ( Production, Production[Category] = "Helmets" )
    ),
    0
)​


    ------------------------------
    Vilmar Santos
    ------------------------------

    Original Message


  • 5.  RE: Calculate the percentage of a column based on one of the values in the column

    Posted 13 days ago

    I was able to get it working.  Here is the solution:

    CY% =

    VAR _myPercent =
        CALCULATE(
            SUM(Production[Sales]),
            FILTER(ALLSELECTED(Production), Production[Category] = "Helmets"),
            FILTER(ALLSELECTED(Production), Production[Week] <= Max(DatesTable[CurrentWeek])),
            FILTER(ALLSELECTED(Production), Production[Year] = Max(DatesTable[CurrentYear]))
        )  
    Return
   
        CALCULATE(
            SUM(Production[Sales]),
            FILTER(Production, Production[Week] <= Max(DatesTable[CurrentWeek])),
            FILTER(Production, Production[Year] = Max(DatesTable[CurrentYear]))
        )  
        /
        _myPercent


    ------------------------------
    Mike McLean
    ------------------------------

    Original Message