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
kuczkrz
Regular Visitor

Issue with data summing up to month/year from product level

Hi all,

 

I have a a simple fact query as follows. Netto is a fact measure set up to summarize as SUM.

kuczkrz_0-1610815272436.png

All I need to do is to create a simple calculations to present MRRs

I was able to complete CurrentMonth, LastMonth, Exisiting and New values with calculations as follows:

 

CurrentMonth = SUM('wFirma InvoiceContents'[Netto])
 
LastMonth = CALCULATE(SUM('wFirma InvoiceContents'[Netto]), PARALLELPERIOD('Daty'[Date], -1,MONTH))
 
ExistingIF ( 'wFirma InvoiceContents'[LastMonth] > 0,  IF ( 'wFirma InvoiceContents'[CurrentMonth] < 'wFirma InvoiceContents'[LastMonth], 'wFirma InvoiceContents'[CurrentMonth], 'wFirma InvoiceContents'[LastMonth] ) )
 
New = CALCULATE( SUM('wFirma InvoiceContents'[Netto]) , FILTER('wFirma InvoiceContents', 'wFirma InvoiceContents'[IsFirstOfThisGoodForContractor] = TRUE() ) )

 

Upgrades = IF ([LastMonth] > 0 && [CurrentMonth] > 0 ,  ( IF ( [LastMonth] < [CurrentMonth], [CurrentMonth] - [LastMonth] ) ) )
 
Downgrades = IF ([LastMonth] > 0 && [CurrentMonth] > 0, (IF ( [LastMonth] > [CurrentMonth], [CurrentMonth] - [LastMonth] ) )
 
Reactivations =  IF ('wFirma InvoiceContents'[LastMonth] = 0 && 'wFirma InvoiceContents'[CurrentMonth] > 0, CALCULATE(SUM('wFirma InvoiceContents'[Netto]) , FILTER('wFirma InvoiceContents', 'wFirma InvoiceContents'[IsFirstOfThisGoodForContractor] = FALSE() )))
 
Churn = IF ([LastMonth] > 0 && [CurrentMonth] = 0 , [CurrentMonth] - [LastMonth] )
 
I'm summarizing this data for year & month to present these in last 24 months.
 
 

I have an issue with summing up this values for month. Please find below what I get. 

 
 
 

Przechwytywanie.PNG

I'd like to present data for example as follows:

for grudzień (december) 2018

Exisitng = 3000 

New = 0

Reactivations = 0

Upgrades = 0

Downgrades = 0 

Churn = -4400

 

For styczeń (January) 2019

Exisitng = 3000

New = 0

Reactivations = 4400

Upgrades = 0

Downgrades = 0 

Churn = 0

 

For luty (february) 2019

Existing = 7400

New = 0

Reactivations = 1500

Upgrades = 1860

Downgrades = -925 

Churn = 0

 

Everything is good on a product level, but going up, to month level/year level, some data are presented differently. I know my formulas are causing this issue with Downgrades/Upgrades, Reactivations and Churn, but at this point I do not have a clue how to create these calculations. Could you please help me out, or give me a hint how to start to get this?

 

Any help will be much appreciated.

 

Thank you

 

 
1 ACCEPTED SOLUTION

Hi,

Try this measure

Measure = SUMX(SUMMARIZE(Goods,Goods[GoodId],"ABCD",[Downgrades]),[ABCD])

See the last column of the table below

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

Could you share a PowerBI Desktop file to work with.  Please ensure data is in English.  Also, share a small dataset so that figures can be easily verified.  Kinldy also show your expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

 

I have created a sample model. Please find it here 

 

What I'm trying to show here are some calculations like

- Existing - if a Contractor had specific good at specific price last month, and has this good in current period, then this value is existing

New - if a Contractor is buying a good for the first time (depend this on a IsfirstOfThisGoodForContractor attribute)

Upgrade - if a Contractor is buying more of a good in current month than last month

Downgrade - if a Contractor is buying less of a good in current month than last month

Reactivations - if a Contractor is buying a good that hadn't had last month (but is not a first time buying it - IsfirstOfThisGoodForContractor = FALSE)

Churn - if a Contractor had a good last month, but not buying it current month.

Please find an example below:

 

Przechwytywanie3.PNG

 

My calculations are:

 

LastMonth = CALCULATE(SUM('Invoice Contents'[Netto]), PARALLELPERIOD(Dates[Date], -1,MONTH))
 
CurrentMonth = SUM('Invoice Contents'[Netto])
 
Existing = [LastMonth]
 
New = CALCULATE( SUM('Invoice Contents'[Netto]) , FILTER('Invoice Contents', 'Invoice Contents'[IsfirstOfThisGoodForContractor] = TRUE()) )


Upgrades = IF ([LastMonth] > 0 && [CurrentMonth] > 0 ,  ( IF ( [LastMonth] < [CurrentMonth], [CurrentMonth] - [LastMonth] ) ) )
 
Downgrades = IF ([LastMonth] > 0 && [CurrentMonth] > 0 ,  ( IF ( [LastMonth] > [CurrentMonth], [CurrentMonth] - [LastMonth] )  ) )
 
Reactivations = IF ([LastMonth] = 0 && [CurrentMonth] > 0, CALCULATE( SUM('Invoice Contents'[Netto]) , FILTER('Invoice Contents', 'Invoice Contents'[IsFirstOfThisGoodForContractor] = FALSE())))
 
Churn = IF ([LastMonth] > 0 && [CurrentMonth] = 0 , [CurrentMonth] - [LastMonth] )


My desired outcome would be like this:

Przechwytywanie.PNG

 

Thanks in advance for any hints.

Regards,

Krzysztof

Hi,

Try this measure

Measure = SUMX(SUMMARIZE(Goods,Goods[GoodId],"ABCD",[Downgrades]),[ABCD])

See the last column of the table below

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you Ashish, it worked perefectly in this scenario!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.