cancel
Showing results for
Did you mean:

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

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

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

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

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 )```

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
Frequent Visitor

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]
)```

4 REPLIES 4
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]),
'Date'[Date],
-1,
MONTH)
)
Var ThisMonth = CALCULATE(SUM(MyTable[Value]))

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

Proud to be a Datanaut!

Frequent Visitor

Hi Phil,

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:

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 !!

Employee

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"
),
)

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"
),
)

VAR MomA = DIVIDE((ThisMonthA-LastMonthA),LastMonthA)
VAR MomB = DIVIDE((ThisMonthB-LastMonthB),LastMonthB)

RETURN DIVIDE(MomA + MomB,2)```

Proud to be a Datanaut!

Frequent Visitor

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]
)```

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors