cancel
Showing results for 
Search instead for 
Did you mean: 
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 III
Super User III

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/

View solution in original post

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
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

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.