Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I am trying to show the % of change from one month to the next. The table is SCCDDATA and the column is [cinum]. I have a count of each cinum called [cinum count]. I am using a date table named 'date'[date]. I want to show this in a table with the cinum as the rows and the month as the columns. Displaying the difference from the previous month. Any help is appreciated.
Solved! Go to Solution.
Hi @joshcomputer1,
Suppose your table structure is like:
Please create a calculated table using below formula:
Summarize table =
SUMMARIZE (
SCCDDATA,
SCCDDATA[Date].[MonthNo],
SCCDDATA[cinum],
"total sales", SUM ( SCCDDATA[sales] )
)
Add a calculated column:
Diff = 'Summarize table'[total sales] - LOOKUPVALUE ( 'Summarize table'[total sales], 'Summarize table'[cinum], 'Summarize table'[cinum], 'Summarize table'[MonthNo], 'Summarize table'[MonthNo] - 1 )
Use a matrix to display data.
Best regards,
Yuliana Gu
Hi @joshcomputer1,
Suppose your table structure is like:
Please create a calculated table using below formula:
Summarize table =
SUMMARIZE (
SCCDDATA,
SCCDDATA[Date].[MonthNo],
SCCDDATA[cinum],
"total sales", SUM ( SCCDDATA[sales] )
)
Add a calculated column:
Diff = 'Summarize table'[total sales] - LOOKUPVALUE ( 'Summarize table'[total sales], 'Summarize table'[cinum], 'Summarize table'[cinum], 'Summarize table'[MonthNo], 'Summarize table'[MonthNo] - 1 )
Use a matrix to display data.
Best regards,
Yuliana Gu
I created the summarized table as you described above. I am not able to enter SCCDDATA[Date].[MonthNo]. So I entered SCCDDATA[Date]. Any idea why I can't do that?
Summarize table =
SUMMARIZE (
SCCDDATA,
SCCDDATA[Date],
SCCDDATA[cinum],
"CI Total", count ( SCCDDATA[cinum] )
)
I think this is causing issues on the Month over Month formula. I have this, but get an error when I include .[MonthNo] on the last line. Obviously, without this, it doesn't slice by month. Any help is appreciated.
Month Over Month =
'Summarize table'[CI Total]
- LOOKUPVALUE (
'Summarize table'[CI Total],
'Summarize table'[cinum], 'Summarize table'[cinum],
'Summarize table'[Date], 'Summarize table'[Date] - 1
)
Hi @joshcomputer1,
I guess you linked 'SCCDDATA' with a date dimension table based on [Date] field. In that case, please try this:
Summarize table = SUMMARIZE ( SCCDDATA, MONTH(SCCDDATA[Date]), SCCDDATA[cinum], "cinum count", COUNT ( SCCDDATA[cinum] ) )
Regards,
Yuliana Gu
I sent you a private message with file path. Please review.
Thanks v-yulgu-msft,
The original table doesn't have a count of each cinum. There are many columns in this table and the cinum column is all text entries. That's why I am using ci count just to get a count of each type of cinum. Does that make a difference in the formula you presented?
cinum month
a 5
b 5
c 6
d 7
a 5
c 8
etc...
Hi @joshcomputer1,
Share a sample dataset and show your expected result.
Hi @joshcomputer1,
In that case, you should modify the formula for calculated table as below:
Summarize table = SUMMARIZE ( SCCDDATA, SCCDDATA[Date].[MonthNo], SCCDDATA[cinum], "cinum count", COUNT ( SCCDDATA[cinum] ) )
The rest steps are the same.
Regards,
Yuliana Gu
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |