Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
GuestUser
Helper V
Helper V

Cumulative dax query help

Hi
We are using power bi with ssas tabular model using connect live option.
Need help in cumulative column
Report format is as below

Matrix view

Year. 2018. 2019
Item. Trxcount. Cumulative Trx...... Cumul
A. 10. 10. 11. 11
B. 20. 30. 22. 33
C. 30. 60. 33. 66

Need help is writing dax query for cumulative measure
1 ACCEPTED SOLUTION

Hi @mwegener 

 

@Thanks for your inputs!!

Used below formula(similar)..replaced selected value by hasonevalue and it worked..

 

Thanks !!

Measure =
CALCULATE (
    [Sales],
    FILTER (
        ALLSELECTED ( 'Table'[Category] ),
        'Table'[Category] <= SELECTEDVALUE ( 'Table'[Category] )
    )
)

 

View solution in original post

23 REPLIES 23

Hi @GuestUser ,

 

try this.

 

Cumulative Trx = 
CALCULATE(
	[Trxcount],
	FILTER(
		ALLSELECTED('Item'[Item]),
		ISONORAFTER('Item'[Item], MAX('Item'[Item]), DESC)
	)
)

 

 

PBIX

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Hi @mwegener 

 

While using MAX function , i get the error ...max function can work only on numbers or dates and not string data types

I am using Connect Live (SSAS Tabular)

 

just for trying....I checked on pbix file where  import method was used ...it works there...

Any idea ..why is it so??

 

Any suggestions on how to implement in connect live?

 

Hi @GuestUser ,

 

I am currently not working with SSAS Tabular, but it is documented in the DAX Guide as follows.

Returns the largest value in a column, or the larger value between two scalar expressions. Ignores logical values. Strings are compared according to alphabetical order.

https://dax.guide/max/

 

Which version are you using?

 

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Hi @mwegener 

 

Ssas 2016

 

Report server - Jan 2020 Version

Hi @GuestUser ,

 

the same problem is described here.

https://community.powerbi.com/t5/Service/Running-Total-Issue/td-p/964702

Unfortunately, the SELECTEDVALUE solution is only available from SSAS 2017.

https://dax.guide/selectedvalue/

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Hi @mwegener 

 

@Thanks for your inputs!!

Used below formula(similar)..replaced selected value by hasonevalue and it worked..

 

Thanks !!

Measure =
CALCULATE (
    [Sales],
    FILTER (
        ALLSELECTED ( 'Table'[Category] ),
        'Table'[Category] <= SELECTEDVALUE ( 'Table'[Category] )
    )
)

 

Hi @GuestUser ,

 

I am glad that your problem has been resolved, you could accept your own reply to close the case.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

hi,

 

facing some strange behaviour

 

the below formula works

Cumulative = calculate( table[total sales],

filter

(allselected(item[item_name]),

item[item_name]<=if(hasonevalue(item[item_name]),values(item[item_name])

))

 

when i dont put any filter condition on total sales..cumulative value is correct...but it shows item with no sales as well

 

So when i put filter condition as total sales is not blank in report ,

then the cumulative value shows wrong value..it shows correct for 2 records..then the third record..it shows the same value as total sales

 

Any suggestions pls?

 

 

Hi @GuestUser,

 

can you provide a screenshot?

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


withoutfilter.jpgWithFilter.jpg

Hi @mwegener

 

First screenshot is without any filter condition added -- it giving correct result

 

Second one - Added filter condition on filter pane ..like trx count is not blank...it gives incorrect result

 

basically i do not need those items in the report with trx count blank

 

Any suggestions pls

Hi @GuestUser ,

 

can you post the DAX formula for both measures?

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Hi,

 

1) Cumulative = calculate( table[trx count],

filter

(allselected(item[item_name]),

item[item_name]<=if(hasonevalue(item[item_name]),values(item[item_name])

))

 

2) Trx Count = sum(table.item_count)

 

Hi @mwegener ,

 

1) Cumulative = calculate( table[trx count],

filter

(allselected(item[item_name]),

item[item_name]<=if(hasonevalue(item[item_name]),values(item[item_name])

))

 

2) Trx Count = sum(table.item_count)

 

Hi @GuestUser ,

 

I don't understand why the "cumulative count" for some items in screenshot is 0.

Here I would expect the value of the previous item.

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Hi @mwegener ,

 

Thanks for your inputs!!

 

Changed formula for Trx Count 

Trx_Count = if(sum(Table[Item_count]) = blank(),0,sum(table[item_count])) ----  Shows 0 instead of blank

And Cumulative values show..previous value where Trx_Count is 0

 

But still I am not able to get rid of records having Trx_Count=0 , when i apply filter..it gives incoorect cumulative value...as posted in previous screenshot

Hi @GuestUser 

 

I have created the following sample PBIX.
Why are these rows in your screenshot 0?

CumTrxItem.png

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Mariusz
Community Champion
Community Champion

Hi @GuestUser 

 

Try this pattern.

Cumulative Quantity :=
CALCULATE (
    SUM ( Transactions[Quantity] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

 Read this article for more info 
https://www.daxpatterns.com/cumulative-total/

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn



Hi @Mariusz 

 

have tried this and its giving same value for all items same like transaction count

 

need the cumulative value item wise

 

any suggestion pls

Hi @GuestUser ,

 

You need to group by Year with ALLEXCEPT() function.

Refer to the following DAX:

Measure =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Year] ),
        'Table'[Value] <= MAX ( 'Table'[Value] )
    )
)

Here is the result.

2-1.PNG

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors