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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sajal161292
Helper V
Helper V

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

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
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

8 REPLIES 8
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

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)

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.

Also please find the link of my pbi file :

 

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

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
If this post helps, please consider accept as solution to help other members find it more quickly.
Greg_Deckler
Super User
Super User

Perhaps a measure constructed such as:

 

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

?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi,

 

This is not working for me!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.