Forum Discussion
Month over Month with multiple text values
I’m trying to create a Month over Month percentage in a matrix and every approach I take results in one error or another.
This is the layout of the data I am using.
To complicate matters further, the rows in the matrix need to be made up of the themes “Attendance, Classwork, Homework, and Sports” showing the change in “Above Average, Average, and Below Average” MoM but due to the cells having multiple values I unpivoted the columns in order to separate the values. I’m not sure if this is causing the issues with calculating the MoM%
Another potential issue, is that the data besides Date, is all text. I attempted to fix this using the Measure:
Average to Count = DISTINCTCOUNT('Sheet1'[Average])
To convert each score (Above Average, Average, Below Average) to a number and then used the Dax:
MoM_Percentage Average =
VAR CurrentMonthCount = [Average to Count]
VAR PreviousMonthCount = CALCULATE([Average to Count], DATEADD('Sheet1'[Month].[Date], -1, MONTH))
RETURN
DIVIDE(CurrentMonthCount - PreviousMonthCount, PreviousMonthCount, 0)
This however, returns 0 for all themes.
Another approach I used was
Count of Average MoM%=
IF(
ISFILTERED('Sheet1'[Average]),
VAR __PREV_MONTH =
CALCULATE(
COUNTA('Sheet1'[Average]),
DATEADD('Sheet1'[Month].[Date], 1, MONTH)
)
RETURN
DIVIDE(COUNTA('Sheet1'[Average]) - __PREV_MONTH, __PREV_MONTH)
)
But again, this results in 0%
I am not sure where I’m going wrong, any help would be greatly appreciated. Thank you
Hi Lucy01,
Issue is with the table format which you're showing in screenshot.
Your data should look like this:-
| Student | Month | Rating | Theme | | ------- | ---------- | ------------- | ---------- | | Adam | 01/01/2024 | Above Average | Homework | | Adam | 01/01/2024 | Above Average | Classwork | | Adam | 01/01/2024 | Average | Sports | | Adam | 01/01/2024 | Average | Homework | | Adam | 01/01/2024 | Below Average | Attendance | | Adam | 01/01/2024 | Below Average | Classwork |Firstly Select below columns and unpivot them
- Above Average
- Average
- Below Average
Once you do that then use split by delimiter on themes column (to Rows)
Transform → Split Column → By Delimiter
Delimiter = ;
select - Split into Rows
Create a calendar table
Calendar =
CALENDAR(
MIN('Sheet1'[Month]),
MAX('Sheet1'[Month])
)Create relationship between date table and your table
and then create dax for current month previous month and MOM%
Total Count = COUNTROWS('Sheet1')Previous Month Count = CALCULATE( [Total Count], DATEADD('Calendar'[Date], -1, MONTH) )MoM % = VAR CurrentMonth = [Total Count] VAR PreviousMonth = [Previous Month Count] RETURN DIVIDE( CurrentMonth - PreviousMonth, PreviousMonth )Now you can use these in your visuals and you'll get the correct output.
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!
7 Replies
Hi Lucy01,
Issue is with the table format which you're showing in screenshot.
Your data should look like this:-
| Student | Month | Rating | Theme | | ------- | ---------- | ------------- | ---------- | | Adam | 01/01/2024 | Above Average | Homework | | Adam | 01/01/2024 | Above Average | Classwork | | Adam | 01/01/2024 | Average | Sports | | Adam | 01/01/2024 | Average | Homework | | Adam | 01/01/2024 | Below Average | Attendance | | Adam | 01/01/2024 | Below Average | Classwork |Firstly Select below columns and unpivot them
- Above Average
- Average
- Below Average
Once you do that then use split by delimiter on themes column (to Rows)
Transform → Split Column → By Delimiter
Delimiter = ;
select - Split into Rows
Create a calendar table
Calendar =
CALENDAR(
MIN('Sheet1'[Month]),
MAX('Sheet1'[Month])
)Create relationship between date table and your table
and then create dax for current month previous month and MOM%
Total Count = COUNTROWS('Sheet1')Previous Month Count = CALCULATE( [Total Count], DATEADD('Calendar'[Date], -1, MONTH) )MoM % = VAR CurrentMonth = [Total Count] VAR PreviousMonth = [Previous Month Count] RETURN DIVIDE( CurrentMonth - PreviousMonth, PreviousMonth )Now you can use these in your visuals and you'll get the correct output.
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!Thank you but that hasn't worked either. Sorry if it's unclear in my original post, but your instructions on which columns to unpivot are the ones I have already done, and I have followed the steps for the delimiter/split rows as well
Hi,
Share data in a format that can be pasted in an MS Excel file. Please also show the expected result.
Hello Lucy01,
Unpivoting was the right move so don't worry about that!
You've got two issues causing the zeros:
DISTINCTCOUNT is the wrong function here. After unpivoting, each row represents a single theme. Since there are only four possible unique values, the DISTINCTCOUNT can’t go higher than 4. That means when you compare month‑over‑month, the difference between 4 and 4 will always come out as zero. Replace it with COUNTA.
DATEADD also needs a proper Date table, pointing it at Sheet1[Month] directly causes it to silently fail. Add one quickly via Modeling → New Table:
DateTable = CALENDAR(MIN(Sheet1[Month]), MAX(Sheet1[Month]))Right-click → Mark as Date Table, then link DateTable[Date] to Sheet1[Month]. Then your measures become:
Theme Count = COUNTA('Sheet1'[Theme]) MoM Count % = VAR CurrentMonth = [Theme Count] VAR PreviousMonth = CALCULATE([Theme Count], DATEADD(DateTable[Date], -1, MONTH)) RETURN DIVIDE(CurrentMonth - PreviousMonth, PreviousMonth, BLANK())Also make sure your matrix slicer is pulling Month from DateTable, not Sheet1
Hi Lucy01 ,
Thank you for reaching out to Microsoft Fabric Community and Thanks to grazitti_sapna , Ashish_Mathur and Olufemi7 for Sharing valuable insights.
Just wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.Best Regards,
Abdul Rafi.
Hi Lucy01 ,
We wanted to check if your question has been resolved or if you are still facing any confusion feel free to reach out. Providing an update can be beneficial for others who might be experiencing similar challenges.Best Regards,
Abdul Rafi
