Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
JohnBI
Frequent Visitor

Calculate average of month-on-month percentage, for all categories, same date

Hi,

 

A "simplified" version of my problem, which consists of 3 columns, namely CATEGORY, DATE, VALUE

 

Sample RAW DATA

 

raw.JPG

 

My goal is to get : Average of month-on-month percentage for category A and B, for each row (DATE in my case)

 

final.JPG

 

----------------------------------------------------------------

 

This is what I have achieved so far. The table below is not my final goal. The final goal is the table above, as noted.

 

(Please note the "DATE[Date]" column below is from lookup calendar table, with relationship.)

 

SUM = SUM ( MYTABLE[VALUE] )
MoM% = 
VAR Last = [SUM]
VAR First = CALCULATE ( [SUM], DATEADD ( 'DATE'[Date], -1, DAY ) )

RETURN
100 * ( Last / First - 1 )

 

MID.JPG

 

The real life problem requires me to get weighted average of data (vs simple average here), and for more than 20 different data in CATEGORY column (vs only two data here, namely A and B). I simplify the problem jz to make your life easy. Many thanks !!

1 ACCEPTED SOLUTION

Hmm....guess that would work too but its not practical for many data in the CATEGORY column.

 

Anyway thanks Phil, I have worked it out alrdy

 

Fact table name: ONE

Date table name: DATE

 

SUM = 
SUM ( ONE[VALUE] )

MoM = 
VAR Last = [SUM]
VAR First = CALCULATE ( [SUM], DATEADD ( 'DATE'[Date], -1, DAY ) )

RETURN
IF ( NOT ( ISBLANK ( First ) ),
	100 * ( DIVIDE ( Last, First ) - 1 )
)

MoM_AVG = 
AVERAGEX ( 
    SUMMARIZE ( ONE, ONE[CATEGORY] ),
    [MoM]
)

Capture.JPG

 

Thanks for your help Phil

View solution in original post

4 REPLIES 4
Phil_Seamark
Employee
Employee

Try this code and make sure you are using the Date column from your Date table on visual and not the date column from MyTable.  Don't forget to format the measure as a percent and adjust the number of decimal points.

 

I might not have the calc right for you in the last line, but you can see it's easy enough to adjust.

 

Measure = 
VAR LastMonth = CALCULATE(
					SUM(MyTable[Value]),
					DATEADD(
							'Date'[Date],
							-1,
							MONTH)
					)
Var ThisMonth = CALCULATE(SUM(MyTable[Value]))					

return DIVIDE((LastMonth-ThisMonth),ThisMonth,0)		

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi Phil,

 

First of all, many thanks for your quick reply !!

 

I think I might have explained it wrongly, hence your solution doesnt work after I alter the value of CATEGORY B to very different values, as shown below.

 

Actually, my problem can be solved very easily in EXCEL, as followed:

 

 

Capture.JPG

 

 

The COLUMNS HIGHLIGHTED IN RED are my final goals.

(COLUMNS DATE, A, B here are the raw data, as noted in my first post, just that I unpivot/pivot with Query editor)

 

In PBI, I think I could have done it by using CALCULATED COLUMN (instead of MEASURES) with EARLIER function, but I would like to use MEASURES as it is more versatile for later use.

 

Hope I successfully clarify my issue. Thanks in advance !!

I could have broken these out to separate measures but give this a try.

 

 

Averaqe of MoM% for A & B = 
VAR ThisMonthA = CALCULATE( SUM('MyTable'[Value]) ,FILTER( ALLEXCEPT(MyTable,'Date'[Date]),[Category]="A") ) VAR LastMonthA = CALCULATE( SUM('MyTable'[Value]), FILTER( ALLEXCEPT('MyTable',MyTable[Date]),[Category]="A" ), DATEADD('Date'[Date],-1,MONTH) ) VAR ThisMonthB = CALCULATE( SUM('MyTable'[Value]) ,FILTER( ALLEXCEPT(MyTable,'Date'[Date]),[Category]="B") ) VAR LastMonthB = CALCULATE( SUM('MyTable'[Value]), FILTER( ALLEXCEPT('MyTable',MyTable[Date]),[Category]="B" ), DATEADD('Date'[Date],-1,MONTH) ) VAR MomA = DIVIDE((ThisMonthA-LastMonthA),LastMonthA) VAR MomB = DIVIDE((ThisMonthB-LastMonthB),LastMonthB) RETURN DIVIDE(MomA + MomB,2)

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hmm....guess that would work too but its not practical for many data in the CATEGORY column.

 

Anyway thanks Phil, I have worked it out alrdy

 

Fact table name: ONE

Date table name: DATE

 

SUM = 
SUM ( ONE[VALUE] )

MoM = 
VAR Last = [SUM]
VAR First = CALCULATE ( [SUM], DATEADD ( 'DATE'[Date], -1, DAY ) )

RETURN
IF ( NOT ( ISBLANK ( First ) ),
	100 * ( DIVIDE ( Last, First ) - 1 )
)

MoM_AVG = 
AVERAGEX ( 
    SUMMARIZE ( ONE, ONE[CATEGORY] ),
    [MoM]
)

Capture.JPG

 

Thanks for your help Phil

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.