Forum Discussion

theo's avatar
theo
Helper III
9 years ago
Solved

measure

i would like to know how to call out another measure to use in another measure. i have created below: count_product = SUMX(all('table'[column1]),calculate(COUNT('table'[Column1]))) and cumulative...
Need Help
  • Sean's avatar
    Sean
    9 years ago

    Hope this helps! :smileyhappy:

     

    New Table =
SUMMARIZE (
    'Table',
    'Table'[Type],
    "Count of Type", COUNTROWS ( 'Table' ),
    "Type Rank by Count", RANKX ( ALL ( 'Table'[Type] ), CALCULATE ( COUNTROWS ( 'Table' ) ) )
)

Cumulative Sum Column =
CALCULATE (
    SUM ( 'New Table'[Count of Type] ),
    FILTER (
        'New Table',
        'New Table'[Type Rank by Count] <= EARLIER ( 'New Table'[Type Rank by Count] )
    )
)

     

    Continued here...

    http://community.powerbi.com/t5/Desktop/cumulative-sum/m-p/136762/highlight/false#M58688

Sean's avatar
Sean
Community Champion
9 years ago

Post the formula you are using

theo's avatar
theo
Helper III
9 years ago
Sean wrote:

Post the formula you are using

here is what i have. i just copied your code

 

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    Hi theo,

     

    I see the issue.  Seans instructions are 2 steps that need to be done separately.

     

    First create a table like this 

     

    New Table =
SUMMARIZE (
    'Table',
    'Table'[Type],
    "Count of Type", COUNTROWS ( 'Table' ),
    "Type Rank by Count", RANKX ( ALL ( 'Table'[Type] ), CALCULATE ( COUNTROWS ( 'Table' ) ) )
)

    and then once you have the table, create a column on that table like this

     

    Cumulative Sum Column =
CALCULATE (
    SUM ( 'New Table'[Count of Type] ),
    FILTER (
        'New Table',
        'New Table'[Type Rank by Count] <= EARLIER ( 'New Table'[Type Rank by Count] )
    )
)
    • theo's avatar
      theo
      Helper III
      9 years ago

      thanks, i tried but i have different result from what is expected.  the rank is 1 for all, see below:

       

      in addition, i realized the result will be fixed bu i need something that could change at a later stage.  i have another column for the product code, then new ones will be added later.  i will then need the cumulative sum based on the new column (product code) and not the type.  thank you so much for providing time to help.

      • theo's avatar
        theo
        Helper III
        9 years ago

        Sean and Phil_Seamark

        thanks, i tried but i have different result from what is expected.  the rank is 1 for all, see below:

         

        in addition, i realized the result will be fixed bu i need something that could change at a later stage.  i have another column for the product code, then new ones will be added later.  i will then need the cumulative sum based on the new column (product code) and not the type.  thank you so much for providing time to help.