Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi! I'm new to Power BI and have struggled with a problem for three days now 😕
I'm trying to recreate my Excel table in Power BI looking like this
The three first rows is the latest stock value in each month.
Forecasted: Is the latest forecasted value we think we need.
The last row is the three first row minus Forecasted row.
This is a sample of my two tables i'm using (The yellow marks is the latest value for each category)
In this case I want the yellow values to be in the table above.
Then I want all the last categories of each month summarized minus the latest Forecasted value.
I suppose this could be done with a Matrix table.
/ Cheers
Solved! Go to Solution.
Hi @royalswe
to solve this, I have created a sample report.
A short description of this file:
I have created two additional tables, vDate and MatrixRows. MatrixRows is necessary in order to get 'Forecasted' and 'Material' on the same axis as the materials.
Then there are 3 measures. The measure for the stock at the end of the month is a bit complex, because your requirement in the last row (Material - Forecasted), demands that the stock is calculated for each material at the last date for each material:
stock end of month =
SUMX (
ADDCOLUMNS (
VALUES ( Material[Material] );
"m";
VAR _mxDate =
CALCULATE ( MAX ( Material[createdDate] ) )
VAR _currentMaterial =
CALCULATE ( VALUES ( Material[Material] ) )
RETURN
CALCULATE (
SUM ( Material[quantity] );
FILTER (
Material;
Material[createdDate] = _mxDate
&& Material[Material] = _currentMaterial
)
)
);
[m]
)
The Forecast measure is pretty simple:
forecast =
VAR _maxDate =
CALCULATE ( MAX ( Forecast[createdDate] ) )
RETURN
CALCULATE (
SUM ( Forecast[quantity] );
FILTER ( vDate; vDate[Date] = _maxDate )
)
And then there is the measure to apply these two measures according to the rows of the matrix:
matrixVariable =
VAR sv =
SELECTEDVALUE ( matrixRows[maRo] )
RETURN
SWITCH (
TRUE ();
sv IN VALUES ( Material[Material] ); CALCULATE ( [stock end of month]; FILTER ( Material; [Material] = sv ) );
sv = "Forecasted"; [forecast];
sv = "Material - Forecasted"; [stock end of month] - [forecast];
BLANK ()
)
Next time you are in need of assistance from the forum, please show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.
Hi @royalswe
to solve this, I have created a sample report.
A short description of this file:
I have created two additional tables, vDate and MatrixRows. MatrixRows is necessary in order to get 'Forecasted' and 'Material' on the same axis as the materials.
Then there are 3 measures. The measure for the stock at the end of the month is a bit complex, because your requirement in the last row (Material - Forecasted), demands that the stock is calculated for each material at the last date for each material:
stock end of month =
SUMX (
ADDCOLUMNS (
VALUES ( Material[Material] );
"m";
VAR _mxDate =
CALCULATE ( MAX ( Material[createdDate] ) )
VAR _currentMaterial =
CALCULATE ( VALUES ( Material[Material] ) )
RETURN
CALCULATE (
SUM ( Material[quantity] );
FILTER (
Material;
Material[createdDate] = _mxDate
&& Material[Material] = _currentMaterial
)
)
);
[m]
)
The Forecast measure is pretty simple:
forecast =
VAR _maxDate =
CALCULATE ( MAX ( Forecast[createdDate] ) )
RETURN
CALCULATE (
SUM ( Forecast[quantity] );
FILTER ( vDate; vDate[Date] = _maxDate )
)
And then there is the measure to apply these two measures according to the rows of the matrix:
matrixVariable =
VAR sv =
SELECTEDVALUE ( matrixRows[maRo] )
RETURN
SWITCH (
TRUE ();
sv IN VALUES ( Material[Material] ); CALCULATE ( [stock end of month]; FILTER ( Material; [Material] = sv ) );
sv = "Forecasted"; [forecast];
sv = "Material - Forecasted"; [stock end of month] - [forecast];
BLANK ()
)
Next time you are in need of assistance from the forum, please show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.
Thank you so much, I had never done this myself.
I'll take your advice and post sample data and my pbix file next time.
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |