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

Hi Sean, i tried this but it says that the DAZ has error.

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 the error.  the first one i think is ok, but hte next call out has an error.

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