Nooby with DAX/PowerBI. In SQL I know how to do, but I could not figure out how to do in Power BI desktop. In the screenshot from Excel I explain what I will achieve. Also the steps I see what are needed for the calculation.
The point is that [c dim] is not in the pivot table. Therefore a SUMX does not have the correct context. [c dim] must be added in the calculation in order to calculate the correct amount in the pivot table.
Hope anyone can help!
Thanks in advance.
Based on your current sample data, you can create a new table using the following formula.
Newtable = ADDCOLUMNS('C dimension',"v1",LOOKUPVALUE(FACT1[v1],FACT1[c dim],'C dimension'[c-dim]),"a dim",LOOKUPVALUE(FACT1[a dim],FACT1[c dim],'C dimension'[c-dim]))
Then create relationship between new table and FACT2 table using c dim field, and create a new column in the newly created table using formula below.
Column = 'Newtable'[v1]*RELATED(FACT2[v2])
This way, you can create a table visual as follows.
For more details about the above steps, please review this attached PBIX file.
Many thanks for your reply. I understand your solution. But I don't think this will solve my issue yet. If I'm correct the creation of Newtable is a static table during slice. This Newtable is refreshed during source data refresh. I need a dynamic table. I will explain why.
Based on your reply I understood my issue definition must be better. So I made a new screenshot, see below.
I added the g dim and r dim.
r dim was added because of a slicer, that excludes R1
g dim was added to show that v1 can be split into two values. Because there are a lot of records a summarize is required.
Does anyone have a solution? Many thanks in advance!
In this sceanrio, you would need to create another table using the following formula.
Table = SUMMARIZE(FACT1,FACT1[a dim],FACT1[c dim],"v1",sum(FACT1[v1]))
Then create another new table based on the above table.
Newtable = ADDCOLUMNS('C dimension',"v1",LOOKUPVALUE('Table'[v1],'Table'[c dim],'C dimension'[c-dim]),"a dim",LOOKUPVALUE('Table'[a dim],'Table'[c dim],'C dimension'[c-dim]))
Please review modified PBIX file.