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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Lejouquard
Frequent Visitor

How to have a right subtotal of a measure which calcultate variation between 2 period

Hi Guys,

 

Need your help please on this topic.

 

In my data base I have sales cumulated amount by month :

 

Lejouquard_0-1614250396488.png

 

My objective is to calculate the sales amount on each period. For that, I have created 2 measures :

 

> Measure 1 : calculation of the cumulated amount with the function PREVIOUSMONTH

> Measure 2 : calculation of the variation : 'sales cumulated amount' - 'Sales cumulated amount M-1'

 

The figures by month are ok BUT the total of my variation is wrong : 

 

Lejouquard_1-1614251087238.png

How I can fix this , and have a right total of my measure 2 ?

Thx for your help!

 

1 ACCEPTED SOLUTION
Portrek
Resolver III
Resolver III

You can use the measure


MEASURE =
CALCULATE (
SUM ( YourTable[collum] ),
FILTER (
ALL ( YourTable),
yourtable[data] <= MAX ( yourtable[data] )
)
)

 

 

See you.

View solution in original post

2 REPLIES 2
Portrek
Resolver III
Resolver III

You can use the measure


MEASURE =
CALCULATE (
SUM ( YourTable[collum] ),
FILTER (
ALL ( YourTable),
yourtable[data] <= MAX ( yourtable[data] )
)
)

 

 

See you.

vmakhija
Post Prodigy
Post Prodigy

@Lejouquard 

You might want to treat totals separately from individual months in your measure definition.

For that, I recommend using HASONEVALUE and ISINSCOPE functions.

Go through the below link to understand how these 2 works -

https://www.sqlbi.com/articles/distinguishing-hasonevalue-from-isinscope/

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.