cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

8 REPLIES 8
Super User
Super User

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, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
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, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 396 members 4,233 guests