Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have a a simple fact query as follows. Netto is a fact measure set up to summarize as SUM.
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:
I have an issue with summing up this values for month. Please find below what I get.
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
Solved! Go to Solution.
Hi,
Try this measure
Measure = SUMX(SUMMARIZE(Goods,Goods[GoodId],"ABCD",[Downgrades]),[ABCD])
See the last column of the table below
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.
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:
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:
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
Thank you Ashish, it worked perefectly in this scenario!
You are welcome.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |