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

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

    Phil_Seamark Sean

    i think i found an issue.  the products column is actually text, i have then created another column where i put 1 in every row and the rank fomula was corrected.

     

    tahnks for all hte help.

  • 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.