Need different calculations at different drill down levels

[Anonymous]

Hi All,

I am trying to calculate Inflation in my report, which is basically (Base Rate - Current Rate) * Current Quantity. But this formula is only valid at Material Level, whenever we go to a different level, Material Group or Category, Inflation should be calculted as SUM of all Inflation at Material level. 

I am trying the measure below,

Inflation YoY (Calculated) =

var curr_rate = [Rate (Calculated)]

var curr_qty = [Qty (Calculated)]

var base_rate =

CALCULATE(

      [Rate (Calculated)],

      DATEADD('Calendar'[Date],-1,YEAR)

)

var inflation =

IF(

    ISBLANK(base_rate),

    BLANK(),

    (base_rate-curr_rate)*curr_qty

)

var temp_table =

ADDCOLUMNS(

    SUMMARIZE(

          RPM,

          RPM[Material]

 ),

   "Inflation",

   inflation

)

return

IF(

    ISINSCOPE(RPM[Material]),

    inflation,

    SUMX(

       temp_table,

       [Inflation])

)

I am getting correct values at Material level, but not at Material Group or Category level. Probably the temp_table I am trying to create is not correct. Any idea where I am going wrong?

Sorry I am not able to upload the PBIX file due to company policy. But I will be glad to share more details if necessary.

Thanks,

Sourav

[Fowmy · Accepted solution]

@Anonymous 
Not quite sure about your data model an what you have in you visual but can you try the following:

Inflation YoY (Calculated) =
SUMX (
    VALUES ( RPM[Material] ),
    VAR curr_rate = [Rate (Calculated)]
    VAR curr_qty = [Qty (Calculated)]
    VAR base_rate =
        CALCULATE ( [Rate (Calculated)], DATEADD ( 'Calendar'[Date], -1, YEAR ) )
    RETURN
        ( base_rate - curr_rate ) * curr_qty
)

[Anonymous]

Hi,

I was wrong, your formula is working perfectly. Thanks a lot.

Sourav

[Anonymous]

Hi,

I think the DAX you shared would be perfect for my Inflation calculation at "Material" level. But when I am trying to get Inflation at Material Group or Category level, it should be the SUM of all Infaltion of all Materials part of that group or category. How would I achieve that in the same measure? That at Material level, it should use the formula (Base Rate - Current Rate)*Current Quantity, but at Group or Category level, it should give SUM of inflation calculated at Material level.

As for my Data Model, it is extremely simple, I just have one Fact table called "RPM", it is connected to just one Date Dimension Table called "Calendar". That's it.

Let me know if you need further details.

Thanks,

Sourav

[ValtteriN]

Hi,

It is hard to visualize this without example data and knowing how your measures work. But basically the wau you can troubleshoot this kind of dilemma is to take the temp table and view it as a calculated table. It is easier to get the correct numbers in a calculated table when you can see the values and when you are satisfied with the logic you can palce your table back into a variable.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

[Anonymous]

Hi,

I tried visualizing the temp_table in a calculated table, like you said, but I am getting same value for each material in that table. I think the filter context is not working in that calculated table.

And sorry for not being able to share the PBIX file or the data, but I can try to explain what I am trying to achieve,

I have 2 Materials, i and ii, below are the numbers I am getting for them,

Material	Base Rate	Current Rate	Current Qty	Inflation
i	65,843.70	80,431.74	70.71	(1,031,506.14)
ii	35,062.74	62,463.33	1,357.56	(37,197,977.42)

The Inflation value I am getting for these Materials are correct, But when I view the data at Material Group level, I should get  (38,229,483.56) as Inflation, which is the aggregate Inflation of i and ii. Instead I am getting  (78,961,822.07) for some reason. Not sure why. This is using the Measure I had shared in my original post.

Thanks,

Sourav