cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lucas-fernandez
Regular Visitor

Help on merging measure and new column behavior

Hello,

 

I am new to using PowerBI for a few weeks. 

 

My system export data that are looking as the following:

 

CategoryProductSubProductTypeQtyWeek
CategoryAProduct1SubProduct1aPlanned100.001
CategoryAProduct1SubProduct1aRequest150.001
CategoryAProduct1SubProduct1aDelta-50.001
CategoryAProduct1SubProduct1bPlanned200.001
CategoryAProduct1SubProduct1bRequest150.001
CategoryAProduct1SubProduct1bDelta50.001
CategoryAProduct2SubProduct2aPlanned300.001
CategoryAProduct2SubProduct2aRequest100.001
CategoryAProduct2SubProduct2aDelta200.001
CategoryAProduct2SubProduct2bPlanned500.001
CategoryAProduct2SubProduct2bRequest50.001
CategoryAProduct2SubProduct2bDelta450.001
CategoryAProduct1SubProduct1aPlanned200.002
CategoryAProduct1SubProduct1aRequest150.002
CategoryAProduct1SubProduct1aDelta50.002
CategoryAProduct1SubProduct1bPlanned60.002
CategoryAProduct1SubProduct1bRequest20.002
CategoryAProduct1SubProduct1bDelta40.002
CategoryAProduct2SubProduct2aPlanned500.002
CategoryAProduct2SubProduct2aRequest50.002
CategoryAProduct2SubProduct2aDelta450.002
CategoryAProduct2SubProduct2bPlanned40.002
CategoryAProduct2SubProduct2bRequest400.002
CategoryAProduct2SubProduct2bDelta-360.002
CategoryAProduct1SubProduct1aRequest250.003
CategoryAProduct1SubProduct1bPlanned300.003
CategoryAProduct1SubProduct1bDelta-50.003
CategoryAProduct2SubProduct2aPlanned280.003
CategoryAProduct2SubProduct2aRequest680.003
CategoryAProduct2SubProduct2aDelta-400.003
CategoryAProduct2SubProduct2bPlanned300.003
CategoryAProduct2SubProduct2bRequest120.003
CategoryAProduct2SubProduct2bDelta1803

 

I am working on a report in which I am displaying in a matrix Category, Product, and Qty 

 

Product / Weeks123
ProductA   
  Delta090-50
  Planned300260300
  Request300170250
ProductB   
  Delta65090-220
  Planned800540580
  Request150450800

 

My issue is that I would like the delta to be running from one week to another. My report is for supply chain usage and I use it to check the impact of variation on coverage. So I would like the matrix to display the following information instead and avoid the addition of an extra column by computing an extra measure.

 

Product / Weeks123
ProductA   
  Delta09040
  Planned300260300
  Request300170250
ProductB   
  Delta650740520
  Planned800540580
  Request150450800

 

So I worked on creating a new column in my data computing the running delta using the following dax

 

RunningDelta = 
IF(OR('Product'[Type] = "Planned", 'Product'[Type] = "Request"), BLANK(),
CALCULATE(
SUM('Product'[Qty]),
'Product'[Type] = "Delta",
ALLEXCEPT('Product', 'Product'[Product]),
'Product'[Index] <= EARLIER('Product'[Index])
)
)
 
This works well but sadly, as I have subproducts, the data displayed in the matrix is false. I would need to remove all but the last RunningDelta per week per product to be able to display the data properly.
 
 
I was able to do so using a measure:
RDelta = CALCULATE(
VALUES('Product'[RunningDelta]),
FILTER(ALL('Product'[Index]), 'Product'[Index] = MAX('Product'[Index]))
)
 
 
I would like now to merge both the code for my new column and the measure to get all my data displayed in one column in my matrix. Could you please guide me to do so?
1 ACCEPTED SOLUTION

You need 3 measures. The ones with an _ (underscore) are auxiliary and should be hidden.

_TotalQty = Sum( T[Qty] )
_Delta = 
IF( ISINSCOPE( T[Type] ) && SELECTEDVALUE( T[Type] ) = "Delta",
    var MaxVisibleWeek = MAX( T[Week] )
    var Result = 
        CALCULATE(
            [_TotalQty],
            T[Week] <= MaxVisibleWeek
        )
    return
        Result
)
Total Qty = 
IF ( HASONEFILTER( T[Type] ),
    var TheType = SELECTEDVALUE( T[Type] )
    var Result =
        SWITCH( TRUE(),
            TheType in {"Delta"}, [_Delta],
            [_TotalQty]
        )
    return
        Result
)

By the way, T[Week] must be an int (not text).

View solution in original post

3 REPLIES 3
lucas-fernandez
Regular Visitor

Thank you very much. I will now take the time to understand the code in the measures.

lucas-fernandez
Regular Visitor

You need 3 measures. The ones with an _ (underscore) are auxiliary and should be hidden.

_TotalQty = Sum( T[Qty] )
_Delta = 
IF( ISINSCOPE( T[Type] ) && SELECTEDVALUE( T[Type] ) = "Delta",
    var MaxVisibleWeek = MAX( T[Week] )
    var Result = 
        CALCULATE(
            [_TotalQty],
            T[Week] <= MaxVisibleWeek
        )
    return
        Result
)
Total Qty = 
IF ( HASONEFILTER( T[Type] ),
    var TheType = SELECTEDVALUE( T[Type] )
    var Result =
        SWITCH( TRUE(),
            TheType in {"Delta"}, [_Delta],
            [_TotalQty]
        )
    return
        Result
)

By the way, T[Week] must be an int (not text).

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors