cancel
Showing results for 
Search instead for 
Did you mean: 
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 II
Resolver II

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 II
Resolver II

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

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.