Forum Discussion
Calculate table with loop
hello PBI masters,
I am a n00b of PBI and struggling with creation of a calculated table. My starting dataset is a table that, for every item and every month, provides specific information:
item | item description | FY | Month | dimension | Quantity |
ABC | ABC-Description | 2021 | 1 | Ordered | 55 |
ABC | ABC-Description | 2021 | 1 | forecast current month | 60 |
ABC | ABC-Description | 2021 | 1 | forecast m-1 | 70 |
ABC | ABC-Description | 2021 | 1 | forecast m-2 | 70 |
ABC | ABC-Description | 2021 | 1 | forecast m-3 | 100 |
The calculated table I need is caculating, for every item and month, the difference between the row "Ordered" with each of the rows "forecast", i.e. the column "FCA" in the below example.
item | year | month | FCA |
Iterm ABC | 2021 | 1 | Ordered - forecast current month |
item ABC | 2021 | 1 | Ordered - forecast m-1 |
item ABC | 2021 | 1 | Ordered - forecast m-2 |
item ABC | 2021 | 1 | Ordered - forecast m-3 |
as far as I understand, it should require DAX and a loop / for cycle, but have no experience in this language. Anyone that could help me please?
thanks a lot!!!
I suggest you change the structure of the original table in Power Query by pivoting the "Dimension" and "Quantity" columns as follows:
I would then suggest you create new dimension tables for Item using:
Item Table = SUMMARIZE('Source Table', 'Source Table'[item], 'Source Table'[item description])and for Year and Month following this pattern:
Fiscal Year Table = DISTINCT('Source Table'[FY])Set up the model using single direction one-to-many relationships between the dimension tables:
Create the measures you need following this pattern:
Ordered - forecast month = SUM('Source Table'[Ordered]) - SUM('Source Table'[forecast current month])Ordered - forecast m-1 = SUM('Source Table'[Ordered]) - SUM('Source Table'[forecast m-1])Finally set up a matrix using the fields from the dimension tables and the measures. In the formatting pane, under Values -> Options, turn on the option "Switch values on rows" to get:
Attached is the sample PBIX file
2 Replies
I suggest you change the structure of the original table in Power Query by pivoting the "Dimension" and "Quantity" columns as follows:
I would then suggest you create new dimension tables for Item using:
Item Table = SUMMARIZE('Source Table', 'Source Table'[item], 'Source Table'[item description])and for Year and Month following this pattern:
Fiscal Year Table = DISTINCT('Source Table'[FY])Set up the model using single direction one-to-many relationships between the dimension tables:
Create the measures you need following this pattern:
Ordered - forecast month = SUM('Source Table'[Ordered]) - SUM('Source Table'[forecast current month])Ordered - forecast m-1 = SUM('Source Table'[Ordered]) - SUM('Source Table'[forecast m-1])Finally set up a matrix using the fields from the dimension tables and the measures. In the formatting pane, under Values -> Options, turn on the option "Switch values on rows" to get:
Attached is the sample PBIX file