cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RTERCERO
Frequent Visitor

Combined iteration of two measures ( multiply row by row between two measures)

Hello,

Currently migrating a KPI (adherence by SKU) from Excel to PBI and having difficulties, here is the definition:

 

RTERCERO_0-1648910918485.png

 

Where: 

RTERCERO_1-1648911062893.png

 

RTERCERO_2-1648911072963.png

 

Made a replica using columns but is not what im looking for because it is not scalable, or relative to other categories and only works as intended using the participation baseline ( currently a week):

RTERCERO_4-1648914091010.png

 

Made a version with measures and it not working properly even on weeks:
Here is the comparison:

 

RTERCERO_5-1648914651370.png

Here are my Measures:

Planned Amount = 
If(
    CALCULATE(SUMX(SKU_TEST,SKU_TEST[PLAN]),DATESYTD(SKU_TEST[Date]))=0,
    BLANK(),
CALCULATE(SUMx(VALUES(SKU_TEST[PLAN]),Calculate(SUM(SKU_TEST[PLAN]),DATESYTD(SKU_TEST[Date])))))

//////////////////////////////////////////////////////////////////////////////////////////

Real Amount = 
If(
    CALCULATE(SUMX(SKU_TEST,SKU_TEST[PLAN]),DATESYTD(SKU_TEST[Date]))=0,
    BLANK(),
CALCULATE(SUMx(VALUES(SKU_TEST[REAL]),Calculate(SUM(SKU_TEST[REAL]),DATESYTD(SKU_TEST[Date])))))

///////////////////////////////////////////////////////////////////////////////

Adherence = 
If(
    [Planned Amount]=0,
    BLANK(),
IF( 
    DIVIDE([Real Amount],[Planned Amount])>1,
    1,
DIVIDE([Real Amount],[Planned Amount])))

////////////////////////////////////////////////////////////////////////

Total Plan of Selected Period = 
If(
   [Planned Amount]=0,
    BLANK(),
CALCULATE([Planned Amount] ,
ALLEXCEPT(SKU_TEST,
SKU_TEST[Date],SKU_TEST[PROGRAMA],SKU_TEST[DT],SKU_TEST[Product],SKU_TEST[AREA], SKU_TEST[PLANT], 'Calendar'[Date],'Calendar'[Week Number],'Calendar'[Month],'Calendar'[Year])
))

//////////////////////////////////////////////////////////////////////////////////

Participation = 
If(
   [Planned Amount]=0,
    BLANK(),
DIVIDE([Planned Amount],[Total Plan of Selected Period]))

///////////////////////////////////////////////////////////////////////////////////////

SKU Adherence Measure = 'Measure'[Adherence]*[Participation]

 

Basically Im looking to get the result from the columns in the weeks graphs but with the flexibility of Measures.

 

Attaching data samples and Test File https://we.tl/t-U21qCp613h 

 

Appreciate the help in advance.

Best Regards

RT

 

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @RTERCERO ,

 

Change your corresponding measures like below:

Total Plan of Selected Period 2 =
CALCULATE (
    SUM ( SKU_TEST[PLAN] ),
    ALLEXCEPT ( SKU_TEST, SKU_TEST[PLANT], SKU_TEST[Planned Week] )
)
Participation 2 =
IF (
    [Planned Amount] = 0,
    BLANK (),
    DIVIDE ( [Planned Amount], [Total Plan of Selected Period 2] )
)
SKU Adherence Measure 2 = 
VAR t_ =
    ADDCOLUMNS ( SKU_TEST, "a_", [% Adherence], "b_", [Participation 2] )
RETURN
    SUMX ( t_, [a_] * [b_] )

Icey_0-1649155389380.png

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Icey
Community Support
Community Support

Hi @RTERCERO ,

 

Change your corresponding measures like below:

Total Plan of Selected Period 2 =
CALCULATE (
    SUM ( SKU_TEST[PLAN] ),
    ALLEXCEPT ( SKU_TEST, SKU_TEST[PLANT], SKU_TEST[Planned Week] )
)
Participation 2 =
IF (
    [Planned Amount] = 0,
    BLANK (),
    DIVIDE ( [Planned Amount], [Total Plan of Selected Period 2] )
)
SKU Adherence Measure 2 = 
VAR t_ =
    ADDCOLUMNS ( SKU_TEST, "a_", [% Adherence], "b_", [Participation 2] )
RETURN
    SUMX ( t_, [a_] * [b_] )

Icey_0-1649155389380.png

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

RTERCERO
Frequent Visitor

Hello @Icey ,

 

Thanks for the help, this solution works to replicate whats done using columns however, It takes a lot more time to calculate and still does not the flexibility when changing between Days/ Weeks/ Months, Works on weeks only.

 

Q1 Is there another option to make it Faster?

 

Q2 can We make it flexible between Time periods?


I was considering maybe in "Total Plan of Selected Period 2" Is where I have the Oportunity.


What do you think?

 

RT

 

Adding an "ALLSELECTED( SKU_TEST )", did the trick here: "Total Plan of Selected Period 2"

Helpful resources

Announcements
Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Solution Authors