Price and quantity variation with subtotals

[Syndicate_Admin]

Good morning.

I have a problem, either of concept or because of the little knowledge I have of DAX formuation ... (I don't use Power BI, I use Power Pivot.)

I've created a simple example. In this table we have records by families and products in 2 different months.

What I want is:
- A PivotTable in which you select a month and in which you can filter by product and family.
- Get the variation of the amount of the month with the previous month (Amount - Amount m-1). So far so easy.
- Get the part of that variation that is due to price changes (Price - Price m-1)*Quantity m-1
- Get the part of that variation that is due to changes in quantity (Quantity - Quantity m-1)*Price

I want to get to represent this table in a PivotTable.
I just want the data for the month I selected and the data for the previous month to appear to me.

I find the problem mainly in the subtotals and totals, because it does not give me the correct amounts. I try it with the summarize formula, but I don't get it...
The data of the previous month I obtain with the following formula:

Thanks a lot!

[Anonymous · Accepted solution]

Hi @Syndicate_Admin ,

It seems that you want to change the suntotals in matrix visual. 

Based on my test, you could use ISINSCOPE() to change totals as shown below:

Change totals =
IF (
    ISINSCOPE ( 'Table'[Column] ),
    IF ( ISINSCOPE ( 'Table'[Row] ), [value measure], [Row Subtotals measure] ),
    [Column Subtotals measure]
)

Here is an example:

Change totals =
IF (
    ISINSCOPE ( 'Table'[Column] ),
    IF ( ISINSCOPE ( 'Table'[Row] ), 1, "Row sub" ),
    "Column sub"
)

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

[Syndicate_Admin]

Good morning!

I still have the same problem. Would there be a simple solution?

I don't think it's complicated and the example I've given is simple, but I'm going crazy...

Thanks a lot!

[Syndicate_Admin]

@Syndicate_Admin thank you very much for your reply.

Even so, in Power Pivot it does not let me use the ISINCOPE formula. Is that so? Can it only be used in Power BI?

Best regards

Javier

[amitchandak]

@Syndicate_Admin , With help from time intelligence and date table you can get last month data

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
Previous year Month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth(dateadd('Date'[Date],-11,MONTH)))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))

create for price and quantity and use

[Syndicate_Admin]

Thanks @Syndicate_Admin

Reviewing questions from previous users, I had already tried these formulas.

With these formulas I get the Amount, quantity and price of the previous year -> Ok

I have a problem with subtotals and totals.

For example: Valuation of the difference in quantity is: (Quantity - Quantity n-1 )*Price n-1

In P4 there is no variation in Q and therefore the valuation is 0. Ok

In P5 Q varies at -100, therefore the valuation of the difference is -100*3 = -300

But in the valuation in the subtotal F2 I calculate -100*4.5 = -450, when it should be -300

I think the problem I have is that I have to use the SUMMARIZE formula well, but with the formulas you have put me does not leave me ...

I want the valuation calculation to do it line by line at the product level and then add them up at the family level, but what it does is calculate the valuation at the family level.

I don't know if I've explained myself very well...

Thanks a lot!