Forum Discussion
Dynamic comparison of values
Hello team
I have a table that i have the costs of the materials for the full year broken down by month. Each month that I am running the results i add it in the same excel right under the previous month because the data is not too large and other users are also using it for their analysis. The data structure looks something like below. i want to create a matrix or a table where I compare the current period result vs STD and also the current vs the Previous Period (CP-1). Obviously the current now is month 6, but then it will change to 7 and so on, that is why i have created this filtering under the column "Period" >> Current, CP-1, CP-2,........ Which is automtaicaly updated as we are progressing during the year. So i would need to have 4 calculated columns,
1. to show current vs std
2. to show current preriod vs previous period
3. current vs standard percentage
4. Current vs previous period percentage.
Can you please help with the DAX
| Material | Costing Result | Period |
| 123456 | 1,511.22 | STD |
| 123456 | 1,444.64 | CP-3 |
| 123456 | 1,444.64 | CP-2 |
| 123456 | 1,444.64 | CP-1 |
| 123456 | 1,434.44 | Current |
Thanks a lot
Fanis_Georg So perhaps something like this:
Current vs. Standard = VAR __Material = MAX( 'Table'[Material] ) VAR __TableCurrent = FILTER( ALL( 'Table' ), [ResultPeriod] = "Current" ) VAR __TableSTD = FILTER( ALL( 'Table' ), [ResultPeriod] = "STD" ) VAR __Current = SUMX( __TableCurrent, [Costing] ) VAR __STD = SUMX( __TableSTD, [Costing] ) VAR __Result = __Current - __STD RETURN __Result
7 Replies
Fanis_Georg So perhaps something like this:
Current vs. Standard = VAR __Material = MAX( 'Table'[Material] ) VAR __TableCurrent = FILTER( ALL( 'Table' ), [ResultPeriod] = "Current" ) VAR __TableSTD = FILTER( ALL( 'Table' ), [ResultPeriod] = "STD" ) VAR __Current = SUMX( __TableCurrent, [Costing] ) VAR __STD = SUMX( __TableSTD, [Costing] ) VAR __Result = __Current - __STD RETURN __ResultGreg_Deckler your solution seems to be working but I come across another issue now. When I enter the calculate measure to my matrix i would expect a result like with 4 columns, CP-1, Current, STD, Current vs Standard. Instead i have the following, CP-1, Current vs Standard, Current, Current vs Standard, Standard, Current vs Standard. So basicaly the Current vs Standard calculated measure repeats after each and every column of the matrix.
Fanis_Georg Yeah, that's the joy of working with matrix visuals in Power BI. You have 2 options basically. Make everything a measure, so CP-1, Current, STD you would all make measures and then use all measures for the columns in your matrix. The other way is a custom matrix hierarchy:
The New Hotness (Custom Matrix Hierarchy) - Microsoft Fabric Community
Hi,
Based on the table that you have shared, show the expected result very clearly.
do you have date column or other column that will show different period of data? Otherwise how to know which one is the current one?
Hi Fanis_Georg ,
Thank you for reaching out to Microsoft Fabric Community.
Thank you ryan_mayu Ashish_Mathur Greg_Deckler for the prompt response.
I have created a PBIX file by using sample data to replicate the scenario.Please go through the attached PBIX file for your reference.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly.
Thank you.
