Forum Discussion

GGDAC's avatar
GGDAC
Icon for Helper I rankHelper I
6 years ago
Solved

Dvision with mutiple values

Dear Team

I have below table with two columns where first column has various key items and second field has its respective values.

I want to create measure by adding another column /field (% of Sales) in this table which gives me % of each item against one key item value which is "Sales" here

For explanation purpse I have give answers for few key items in column 3 (% of Sales) but I need to know to develop measure which can give me this kind of result in one column

 

Key ItemsValues% of Sales
Sales5000100
Cost of Sales300060
Gross Profit200040
Admin Cost500 
Marketing Cost700 
Depreciation300 
Net Pofit  

 

 
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi GGDAC ,

     

    Try this:

    1. Creating a calculated column

     

    Sales = 
CALCULATE(
    MAX(Sheet1[Values]),
    FILTER(
        Sheet1, 
        Sheet1[Key Items] = "Sales"
    )
)

     

    2. Creating a measure

     

    Measure = 
DIVIDE(
    MAX(Sheet1[Values]),
    MAX(Sheet1[Sales])
)

     

     

    If the value of the “Sales“ is always maximum, you can also do like this:

    Measure 2 = 
DIVIDE(
    MAX(Sheet1[Values]),
    CALCULATE(
        MAX(Sheet1[Values]),
        ALL(Sheet1)
    )
)

     

    Best regards,
    Lionel Chen

    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

    GGDAC , can you explain this 100% calculation and these are columns or measures or the dimension values

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

      Dear Amit

      This 100% is the measure which I want to add in my table as another column

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

    Hi GGDAC ,

     

    Try this:

    1. Creating a calculated column

     

    Sales = 
CALCULATE(
    MAX(Sheet1[Values]),
    FILTER(
        Sheet1, 
        Sheet1[Key Items] = "Sales"
    )
)

     

    2. Creating a measure

     

    Measure = 
DIVIDE(
    MAX(Sheet1[Values]),
    MAX(Sheet1[Sales])
)

     

     

    If the value of the “Sales“ is always maximum, you can also do like this:

    Measure 2 = 
DIVIDE(
    MAX(Sheet1[Values]),
    CALCULATE(
        MAX(Sheet1[Values]),
        ALL(Sheet1)
    )
)

     

    Best regards,
    Lionel Chen

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

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

    Hi,

    Try these measures

    Measure = SUM(Data[Values])
    Measure 2 = [Measure]/CALCULATE([Measure],Data[Key Items]="Sales")

    Hope this helps.