How to do an inter calculation in matrix ?

[Birinder]

Hi there,
Good wishes everyone,

As you can see on the left hand side, there is a matrix which contains discounted and nominal prices for 2 periods. I want to achieve results like the picture shown in right hand side. I want to subtract q2 2021 Discounted and nominal prices from fa 2020 discounted and nominal prices.
A seperate table may also work with seperate columns in the same tables.

I've seen this already in this forum.
I thought I will tag that member to answer my question, but he is not active, since feb 2020. 

[amitchandak · Accepted solution]

@Birinder , from where the fa20 , and q2 2021 is coming?

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[amitchandak · Accepted solution]

@Birinder , If you need diff between two period, separated by 1 period or one year.  With help from period/date table, Period rank and Year  , Period number you can do

example

new column in period or date table

Period Rank = RANKX(all(Period),Period[year period],,ASC,Dense)

measure
This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))
Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1))

This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),'Period'[Year]=max('Period'[Year]) && Period[Period]=max(Period[Period])))
Last year same Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),'Period'[Year]=max('Period'[Year])-1 && Period[Period]=max(Period[Period])))

Actually, I am somewhat confused if you are looking for something other than period diff. Sorry for that

[v-zhangti · Accepted solution]

Hi, @Birinder 

I recovered some data by the picture you gave me, and I hope it will restore your problem.

Measure = 
VAR n1 =
    CALCULATE (
        MAX ( 'Table'[Price] ),
        FILTER (
            'Table',
            [Period] = "Q2 2021"
                && [Currency] = MAX( 'Table'[Currency] )
                && [Methods] = MAX( 'Table'[Methods] )
        )
    )
VAR n2 =
    CALCULATE (
        MAX ( 'Table'[Price] ),
        FILTER (
            'Table',
            [Period] = "FA 2020"
                && [Currency] = MAX('Table'[Currency] )
                && [Methods] = MAX('Table'[Methods] )
        )
    )
RETURN
    n1 - n2

Best Regards,

Community Support Team _Charlotte

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

[v-zhangti · Accepted solution]

Hi, @Birinder 

I simulated some more data, and I hope this time it fits your situation.

Use the function to create a new table.

Table = SELECTCOLUMNS(Dim_facts,"Currencies_ID",[Currencies_ID])

Add calculated columns using lookupvalue.

Price = LOOKUPVALUE(Dim_facts[Price],Dim_facts[Currencies_ID],[Currencies_ID])

Price = LOOKUPVALUE(Dim_facts[Price],Dim_facts[Currencies_ID],[Currencies_ID])

Junk_Dimension_id = LOOKUPVALUE(Dim_facts[Junk_Dimension_id],Dim_facts[Currencies_ID],[Currencies_ID])

Methods = LOOKUPVALUE(Dim_methods[Methods],Dim_methods[Junk_Dimension_id],[Junk_Dimension_id])

Period_id = LOOKUPVALUE(Dim_facts[Period_id],[Currencies_ID],[Currencies_ID])

Period = LOOKUPVALUE(Dim_period[Period],Dim_period[Period_id],[Period_id])

Measure just uses the function in the previous reply.

Best Regards,

Community Support Team _Charlotte

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

[v-zhangti · Accepted solution]

Hi, @Birinder 

This source data is really too large.

By the same token, both Junk_Dimension_id and Period_id can be used as the columns selected for the new table.

Table = SELECTCOLUMNS(Dim_facts,"Junk_Dimension_id",[Junk_Dimension_id])

Table = SELECTCOLUMNS(Dim_facts,"Period_id",[Period_id])

Hope this helps you.

Best Regards,

Community Support Team _Charlotte

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

[v-zhangti · Accepted solution]

Hi, @Birinder 

I recovered some data by the picture you gave me, and I hope it will restore your problem.

Measure = 
VAR n1 =
    CALCULATE (
        MAX ( 'Table'[Price] ),
        FILTER (
            'Table',
            [Period] = "Q2 2021"
                && [Currency] = MAX( 'Table'[Currency] )
                && [Methods] = MAX( 'Table'[Methods] )
        )
    )
VAR n2 =
    CALCULATE (
        MAX ( 'Table'[Price] ),
        FILTER (
            'Table',
            [Period] = "FA 2020"
                && [Currency] = MAX('Table'[Currency] )
                && [Methods] = MAX('Table'[Methods] )
        )
    )
RETURN
    n1 - n2

Best Regards,

Community Support Team _Charlotte

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

[Birinder]

Hi @v-zhangti 
Thank you for such great effort on my problem.
But below I also mentioned that all the tables are from different dimensions. Its a live data. There are dimensions and each dimension has multiple tables and within those tables there lies the data.
Do you any ideas on how we can achieve this ?

[v-zhangti]

Hi, @Birinder 

The fact that you can compose a matrix view like this means that there are associations in each table. You might consider using the LOOKUPVALUE function to aggregate the fields you need into one table.

https://docs.microsoft.com/dax/lookupvalue-function-dax 

In the case of the Measure I did, Currency, Period, and Methods need to appear in a table for easy filtering.

If possible, I still hope you can provide simple PBIX files for testing, which can remove sensitive data in advance.

Best Regards,

Community Support Team _Charlotte

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

[Birinder]

In the case of the Measure I did, Currency, Period, and Methods need to appear in a table for easy filtering.


Yes,yes. You are finally getting there. I also have the same IDEA of doing so.

If possible, I still hope you can provide simple PBIX files for testing, which can remove sensitive data in advance.

I do want to but data is live, I dont have any permission to remove the data. I dont even know how to create a dummy dimension data. Sorry buddy.

I thought LOOKUP VALUE will work. But as soon as I fill the information of one dimension, only fields for those dimension appears. I can't select any other table from different dimension.

[v-zhangti]

Hi, @Birinder 

I thought LOOKUP VALUE will work. But as soon as I fill the information of one dimension, only fields for those dimension appears. I can't select any other table from different dimension.

I think you may not have found the correct use of LOOKUPVALUE. Can you describe to me which fields are common to "Dim_period" and "Dim_facts". Dim_facts" and "Dim_methods", "Dim_methods" and "Dim_currency", what fields are connected in each of these 4 tables, could you please describe them?

Best Regards

[Birinder]

hi @v-zhangti 
Fields which is common to "Dim_period" and "Dim_facts" is "period_id"

Fields which is common to "Dim_facts" and "Dim_methods" is "Junk_Dimension_id"

There is no field directly related to Dim_methods and Dim_Currency. There is a field "Currencies_ID" in Dim_Currency which is connected to "Dim_Facts" and as mentioned above that "Dim_facts" and "Dim_methods" is connected via "Junk_Dimension_id"

[v-zhangti · Accepted solution]

Hi, @Birinder 

I simulated some more data, and I hope this time it fits your situation.

Use the function to create a new table.

Table = SELECTCOLUMNS(Dim_facts,"Currencies_ID",[Currencies_ID])

Add calculated columns using lookupvalue.

Price = LOOKUPVALUE(Dim_facts[Price],Dim_facts[Currencies_ID],[Currencies_ID])

Price = LOOKUPVALUE(Dim_facts[Price],Dim_facts[Currencies_ID],[Currencies_ID])

Junk_Dimension_id = LOOKUPVALUE(Dim_facts[Junk_Dimension_id],Dim_facts[Currencies_ID],[Currencies_ID])

Methods = LOOKUPVALUE(Dim_methods[Methods],Dim_methods[Junk_Dimension_id],[Junk_Dimension_id])

Period_id = LOOKUPVALUE(Dim_facts[Period_id],[Currencies_ID],[Currencies_ID])

Period = LOOKUPVALUE(Dim_period[Period],Dim_period[Period_id],[Period_id])

Measure just uses the function in the previous reply.

Best Regards,

Community Support Team _Charlotte

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

[Birinder]

hi @v-zhangti 
Thank you Thank you Thank you Thank you.
Thank you for all this. 

Now the error is from our side, Cause while using this :-

Table = SELECTCOLUMNS(Dim_facts,"Currencies_ID",[Currencies_ID])

db memory error is showing. Cause the table is heavy in size. It is above 21 gb. while the memory limit is 20.48 gb. Can you give codes of choosing a different table column first and then looking up FACT table through it.

I wish I had knowledge of it.
But thanks to you and Amit, I literally learned a lot from you guys.

[v-zhangti · Accepted solution]

Hi, @Birinder 

This source data is really too large.

By the same token, both Junk_Dimension_id and Period_id can be used as the columns selected for the new table.

Table = SELECTCOLUMNS(Dim_facts,"Junk_Dimension_id",[Junk_Dimension_id])

Table = SELECTCOLUMNS(Dim_facts,"Period_id",[Period_id])

Hope this helps you.

Best Regards,

Community Support Team _Charlotte

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

[Birinder]

Hi @v-zhangti 
But in these both cases too, dim_facts is imported first.
Which again shows the memory error.

What if we can change the token ?
Instead of choosing Dim_facts can't we use another dimension first.

What would be the code for it ?

[v-zhangti · Accepted solution]

Hi, @Birinder 

You can also start by selecting the Dim_Currency table.

Table = SELECTCOLUMNS(Dim_Currency,"Currencies_id",[Currencies_id])

Best Regards,

[amitchandak · Accepted solution]

@Birinder , from where the fa20 , and q2 2021 is coming?

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Birinder]

Hi @amitchandak 
The data is connected lively.
It's quite complex but I can't share you the data due to confideniality concerns for our company.

However, I will answer your every question.

Source of Period is a table which is named as "Period (v)" and it belongs to dimension "Dim_period".

Source of Price is a table which is named as "FACTS" and it belongs to dimension "Dim_facts".

Source of Method is a table which is named as "Method (f)" and it belongs to dimension "Dim_methods".

Source of currency is a table which is named as "Currency USD" and it belongs to dimension "Dim_currency".

[amitchandak]

@Birinder , I wanted to FA and Qtr are different columns in the table, Assume they FY and QTR

We can measure like

This YearQtr= CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[YearQtr]=max('Date'[YearQtr])))
This FY= CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[FY]=max('Date'[FY])))

then we can take diff.

We can use time intelligence for Period can not use time intelligence we can follow rank approach of WOW

refer below if needed

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[Birinder]

hi @amitchandak 
FA & QTR are just values. Consider them as only values. They are not a duration type variables. There is a column which is named as "Period". Under it there are values such as "P6 2021", "P9 2021" and so on.
In matrix, I've used the Period column as a column value. Then I am drillig it down to show me the values for different methods as well.

[Birinder]

Hi @amitchandak 
I forgot to add this before. My problem is same as this.
Except I dont want the multiplication part.
If possible, Can you give me a solution like this.

Solved: Create a new calculated column in matrix - Microsoft Power BI Community

[amitchandak · Accepted solution]

@Birinder , If you need diff between two period, separated by 1 period or one year.  With help from period/date table, Period rank and Year  , Period number you can do

example

new column in period or date table

Period Rank = RANKX(all(Period),Period[year period],,ASC,Dense)

measure
This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))
Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1))

This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),'Period'[Year]=max('Period'[Year]) && Period[Period]=max(Period[Period])))
Last year same Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),'Period'[Year]=max('Period'[Year])-1 && Period[Period]=max(Period[Period])))

Actually, I am somewhat confused if you are looking for something other than period diff. Sorry for that

[Birinder]

hi @amitchandak
Nah, I am sorry too for such weird explanations.
In simple table forms data is like SS below:

Now if, you add a matrix visual.
Drag currency to rows, price to values and "Methods" & "Period" to columns, we get the matrix table, I shared in the very first place.
And yes I want a difference only, I just can't get the logic behind it. Nothin else. 


Now coming on to the latest answer.
Period Rank = RANKX(all(Period),Period[year period],,ASC,Dense)

Here what does "all" refers to? If I have only one period column then why there is an "year period" term in formula? And one more thing, By "Dense" you mean DESC? cause Dense isn't working.