cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sajal161292 Member
Member

To get cumulative only at one field

Hi,

I have prepared a report.I need to get the cumulative value using DAX only at the point where quantity exists

As an example,for the date 12/29/2017 there is a quantity -2 in Planned Order Demand.

I would like to have a field Cummulative as that it should show up the value only at the date where quantity exists(it is not equal to zero) (on 12/29/2017 in our case).

Presently it is representing the value -2 across all the dates.So can you suggest a way of achieving it in DAX?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: To get cumulative only at one field

Hi @sajal161292,

 

You can try to use below measure to check on current quarity:

Check =
IF (
    SELECTEDVALUE ( ShortageData[dt] ) IN ALLSELECTED ( 'Calendar'[Date] ),
    IF ( SUM ( ShortageData[Quantity] ) <> 0, -2, 0 ),
    BLANK ()
)

7.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

8 REPLIES 8
Super User IV
Super User IV

Re: To get cumulative only at one field

Perhaps a measure constructed such as:

 

Measure = IF(ISBLANK([Measure]),0,MAX([Column]))

?


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

sajal161292 Member
Member

Re: To get cumulative only at one field

Hi,

 

This is not working for me!!

Community Support Team
Community Support Team

Re: To get cumulative only at one field

HI @sajal161292,

 

>>I would like to have a field Cummulative as that it should show up the value only at the date where quantity exists(it is not equal to zero) (on 12/29/2017 in our case).

Current you can't customize matrix column group, if each row in current column group is blank, it will auto hide/remove this group column.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
sajal161292 Member
Member

Re: To get cumulative only at one field

Hi,

 

I don't want to hide it .Its just that i need to do some mofification in the data afterwards and for that i need to have the field cummulative showing up the value only at the point where the quantity exists.pow1.PNG

As in the above example only against the date 12/29/2017 and 0 for the others.

MarcelBeug Super Contributor
Super Contributor

Re: To get cumulative only at one field

In general one gets the best help by providing a comprehensive explanation of an issue (rather than posting the same question on multiple forums). And provide better feedback than just "This is not working for me!!".

 

Maybe "Cummulative" is already a calculated field?

 

Anyhow, this might be a suggestion (the small box in the lower right hand corner, is the matrix visual I created):

 

Cumulative.png

Specializing in Power Query Formula Language (M)
sajal161292 Member
Member

Re: To get cumulative only at one field

Hi,

 

My requirement is to pass the current week's total value to the next week's inventory order type.So for that I am calculating the field cummulative for storing the current week's total quantity value.

As you can see in my screenshot it is taking -2 in every date.

 

pow1.PNG

 

 

Because of which it is taking a wrong value for the Inventory order type.

 

pow2.PNG

While i need it to have the value as 0 0 -2 against all the weeks.

 

So could you please any method for achieving this objective.

I am stuck up on this objective from weeks and still not able to figure out any solution to it.

sajal161292 Member
Member

Re: To get cumulative only at one field

Also please find the link of my pbi file :

 

https://drive.google.com/open?id=1XGplFr7YBiTtsNBNQ0Koq6Ea5sApIyHf

Community Support Team
Community Support Team

Re: To get cumulative only at one field

Hi @sajal161292,

 

You can try to use below measure to check on current quarity:

Check =
IF (
    SELECTEDVALUE ( ShortageData[dt] ) IN ALLSELECTED ( 'Calendar'[Date] ),
    IF ( SUM ( ShortageData[Quantity] ) <> 0, -2, 0 ),
    BLANK ()
)

7.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors