DAX: Exclude Negatives in Totals in Higher Hierarchies in Matrix, But do Show Them on Lower Level

[Titatovenaar2]

Hi everyone,

(.PBIX File included.)

I have a matrix with a SUMX measure inside that on a certain level in the hierarchy (i.e.: YearMonth-Plant-Material-Location) everything below zero gets rounded to 0.

This measure shows correct values when the Matrix is Rolled up, however, When I unfold the Matrix to its lowest level, I see that all negative records are rounded to 0. They should only round up to 0 on a higher level.

Below I show in green I show which measure is correct:

• [Closing Stock] is correct when the Matrix is Unfolded.
• [Closing Stock > 0] is correct when the Matrix is Rolled Up.

The measure I currently use to ignore negative values on the level of YearMonth-Plant-Material-Location

Closing Stock > 0 = 
SUMX(
    SUMMARIZE('Fact Inventory', 
        'DIM Calendar'[YearMonth]
        ,'Fact Inventory'[Plant]
        ,'Fact Inventory'[Material]
        ,'Fact Inventory'[Location]
    )
    ,IF(
         [Closing Stock] < 0
        ,0
        ,[Closing Stock]
    )
)

The measure that shows values we would like to view on the level of YearMonth-Plant-Material-Location-Batch (the deepest level of the hierarchy)

Closing Stock = 
SUM('Fact Inventory'[Stock])

The dataset we use these measures on is the following table:

YearMonth	Budat_dtm	Plant	Material	Location	Batch	Movement_Group	Stock
2021-01	2021-01-04	AUB1	11352		NULL	R	-1150.750
2021-01	2021-01-31	AUB1	11352	1000	R000022425	C	-638.323
2021-01	2021-01-04	AUB1	11352	1000	R000022425	C	-271.887
2021-01	2021-01-26	AUB1	11352	1000	R000022425	C	-66.654
2021-01	2021-01-19	AUB1	11352	1000	R000022425	R	648.900
2021-01	2021-01-12	AUB1	11352		NULL	R	-525.550
2021-01	2021-01-03	AUB1	11352	1000	R000022425	C	-896.953
2021-01	2021-01-12	AUB1	11352	1000	R000022425	R	525.550
2021-01	2021-01-08	AUB1	11352	1000	R000022425	R	559.250
2021-01	2021-01-09	AUB1	11352	1000	R000022425	C	-404.594
2021-01	2021-01-07	AUB1	11352	1000	R000022425	C	-232.933
2021-01	2021-01-18	AUB1	11352	1000	R000022425	C	-222.433
2021-01	2021-01-06	AUB1	11352	1000	R000022425	C	-231.428
2021-01	2021-01-08	AUB1	11352		R000022425	S	525.550
2021-01	2021-01-05	AUB1	11352	1000	R000022425	C	-269.349
2021-01	2021-01-20	AUB1	11352	1000	R000022425	C	-218.707
2021-01	2021-01-12	AUB1	11352		R000022425	S	724.250
2021-01	2021-01-18	AUB1	11352		NULL	R	-724.250
2021-01	2021-01-22	AUB1	11352	1000	R000022425	R	1001.100
2021-01	2021-01-24	AUB1	11352	1000	R000022425	C	-724.731
2021-01	2021-01-21	AUB1	11352	1000	R000022425	C	-146.519
2021-01	2021-01-03	AUB1	11352	1000	R000022425	C	60.000
2021-01	2021-01-08	AUB1	11352		NULL	R	-559.250
2021-01	2021-01-18	AUB1	11352		R000022425	S	1001.100
2021-01	2021-01-25	AUB1	11352	1000	R000022425	C	-249.267
2021-01	2021-01-13	AUB1	11352		R000022425	S	648.900
2021-01	2021-01-22	AUB1	11352		NULL	R	-1001.100
2021-01	2021-01-04	AUB1	11352	1000	R000022425	R	1150.750
2021-01	2021-01-04	AUB1	11352		R000022425	S	559.250
2021-01	2021-01-19	AUB1	11352		NULL	R	-648.900
2021-01	2021-01-19	AUB1	11352	1000	R000022425	C	-226.384
2021-01	2021-01-18	AUB1	11352	1000	R000022425	R	724.250
2021-01	2021-01-01	AUB1	11352	1000	R000022425	OPENINGSTOCK	1413.971
2021-01	2021-01-01	AUB1	11352		R000022425	OPENINGSTOCK	1150.750
2021-01	2021-01-01	AUB1	14001	9999	R000033331	R	4000
2021-01	2021-01-01	AUB1	14001	9999	R000033332	S	-5000
2021-01	2021-01-01	AUB1	17005	5000	R000066000	R	4000
2021-01	2021-01-01	AUB1	17005	3000	R000066000	S	-5000

We are dealing with around 100 million records, so Performance is also something to keep in mind.

Is there a way to reveal negatives on the deepest level in a matrix, while rounding up on a different level?

Kind regards,

Igor

[aj1973]

Hi @Titatovenaar2 

How about you keep it simple

[parry2k]

@Titatovenaar2 Awesome. glad it worked out, Subscribe to my YouTube channel where I will be posting uncommon tips and tricks. Cheers!!

✨ Follow us on LinkedIn and subscribe to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Titatovenaar2]

Works like a charm. Epic functionality that I didn't know about yet. Thanks for the fast reply also!

[parry2k · Accepted solution]

@Titatovenaar2 you should ISINSCOPE function to find out what level you are at and then wrap your measure around it.

Closing Stock > 0 = 
IF ( ISINSCOPE ( YourTable[Product] ), //you are at product level
   [Closing Stock],

SUMX(
    SUMMARIZE('Fact Inventory', 
        'DIM Calendar'[YearMonth]
        ,'Fact Inventory'[Plant]
        ,'Fact Inventory'[Material]
        ,'Fact Inventory'[Location]
    )
    ,IF(
         [Closing Stock] < 0
        ,0
        ,[Closing Stock]
    )
)
)

You can change above if condition as you see fit.

✨ Follow us on LinkedIn and subscribe to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡