measure

[theo]

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 sum= SUMX(FILTER(ALL([count_product]),[count_product]<=max([count_product])),[count_product])

[Greg_Deckler]

You probably need to do a SUMMARIZE where you essentially dynamically create a table that effectively has rows in which that measure is evaluated and feed that to your FILTER as the table. Only so much I can do without seeing some sample data but your situation seems similar to one I ran into here:

https://community.powerbi.com/t5/Desktop/Data-Modeling-Issue-Fun/m-p/125007#M52919

[theo]

Here is the visual of my data.  the last column is the one that i am trying to create.

i tried the summarize but then there is an error that says about max when i used the column defined in the summarize

Type	count_product	Cumulative sum
1	500	500
3	330	830
2	220	1050

[Sean · Accepted solution]

Hope this helps!

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]

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

[Phil_Seamark]

Hi @theo,

What is the error?  Do your Table and Column names match the formula posted by @Sean ?

[Sean]

Post the formula you are using

[theo]

---

@Sean wrote:

Post the formula you are using

---

here is what i have. i just copied your code

[Phil_Seamark]

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]

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]

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

[theo]

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

[Phil_Seamark]

Nice work @theo

[theo]

---

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.