Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 , 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
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.
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.
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.
Hi, @Birinder
You can also start by selecting the Dim_Currency table.
Table = SELECTCOLUMNS(Dim_Currency,"Currencies_id",[Currencies_id])
Best Regards,
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.
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 ?
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.
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.
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
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"
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.
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.
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.
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 ?
Hi, @Birinder
You can also start by selecting the Dim_Currency table.
Table = SELECTCOLUMNS(Dim_Currency,"Currencies_id",[Currencies_id])
Best Regards,
@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.
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".
@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
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.
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
@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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
148 | |
116 | |
104 | |
89 | |
65 |