Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Measure Column By Category

Hello guys,

 

Can you help me with this one?

 

I have a table as shown below:

 

so basically, what I want to make is a new measurement like the configuration column on the above image.

thank you

Need Help
Show and Tell
Tips and Tricks
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario.

    Table:

     

    You may create measures as follows.

     

    Configuration % = 
DIVIDE(
    SUM('Table'[Total Unit]),
    CALCULATE(
        SUM('Table'[Total Unit]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Year] = MAX('Table'[Year])
        )
    )
) 

Take up = 
DIVIDE(
    SUM('Table'[Sold]),
    SUM('Table'[Total Unit])
)

Remaining = 
SUM('Table'[Total Unit]) - SUM('Table'[Sold])

Configuration of Remaining Unit % = 

    DIVIDE(
      [Remaining],
      CALCULATE(
          [Remaining],
          FILTER(
              ALLSELECTED('Table'),
              'Table'[Year] = MAX('Table'[Year])
          )
      )
    )

     

     

    Result:

     

    Best Regards

    Allan

     

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

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Try

    configuration % = divide(sum(table[Total Units]),calculate(sum(table[Total Units]),all(table)))
Remaning = sum(table[Total Units]) -sum(table[sold])	
configuration % = divide(sum(table[Total Units]),calculate(sum(table[Total Units]),all(table)))
take up =divide(sum(table[sold]),sum(table[Total Units]))

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blog -
    Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
    Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

    Connect on Linkedin

  • Razwan's avatar
    Razwan
    Icon for Helper I rankHelper I
    6 years ago

    Anonymous 
    Hi, since you need the (%) by year, you may use these measures:

    # Total Units = SUM ('YourTable'[Total Unit])
    # Total Units Per Year = 
    CALCULATE(
        [# Total Units],
            ALLEXCEPT( 'YourTable',
                'YourTable'[Year],
                'YourTable'[Cluster],
                'YourTable'[Sub Cluster]
            )
    )
    # Configuration (%) = DIVIDE( [# Total Units], [# Total Units Per Year], 0 )

     
    Kind regards,
    Razwan

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario.

    Table:

     

    You may create measures as follows.

     

    Configuration % = 
DIVIDE(
    SUM('Table'[Total Unit]),
    CALCULATE(
        SUM('Table'[Total Unit]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Year] = MAX('Table'[Year])
        )
    )
) 

Take up = 
DIVIDE(
    SUM('Table'[Sold]),
    SUM('Table'[Total Unit])
)

Remaining = 
SUM('Table'[Total Unit]) - SUM('Table'[Sold])

Configuration of Remaining Unit % = 

    DIVIDE(
      [Remaining],
      CALCULATE(
          [Remaining],
          FILTER(
              ALLSELECTED('Table'),
              'Table'[Year] = MAX('Table'[Year])
          )
      )
    )

     

     

    Result:

     

    Best Regards

    Allan

     

    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
      6 years ago

      It works!

      Thank you for your help.

       

      Sorry if my questions aren't that clear.