Forum Discussion

Mike282's avatar
Mike282
Icon for Helper III rankHelper III
7 years ago
Solved

Subscription Purchase reduction or cancellation DAX

Hi all,   I've got a question regarding what I can do in Power BI to determine subscription cancellation and module reduction.   UPDATE: You can download the sample PBIX file here from DropBox. ...
Need Help
Tutorial Requests
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

    I see no difference between the Invoice and InvoiceItem table.  WIth the relationship established correctly, you just have to edit all my measures to refer to the Invoiceitem table (rather than the Invoice Table).

    Please try that yourself.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

    Try this measure

    =SUMX(FILTER(SUMMARIZE(VALUES(Invoice[Customer_ID]),[Customer_ID],"ABCD",[Subscriptions],"EFGH",[Subscriptions in previous month]),[ABCD]>0&&[ABCD]<[EFGH]),[EFGH]-[ABCD])

    Hope this helps.

     

Mike282's avatar
Mike282
Icon for Helper III rankHelper III
7 years ago

Thanks Ashish_Mathur 

 

I think it works. The problem is that I can verify the data in a table visual because it couldn't load. It's giving a error saying that there's not enough memory to complete this operation.

 

The dataset I'm applying this measure to counts up to 183k reords over 3 years. Also it looks like when I Group the measure by month in a table it's giving really low numbers (like 23) for Cancelled but when I export the data and run a pivot I'm getting like over 500 plus cancelled subscriptions for the period of February when comparing with January data.

 

Kind regards,

Michael

Mike282's avatar
Mike282
Icon for Helper III rankHelper III
7 years ago

I tried the DAX and it doesn't seem to bring in the right numbers. It's alot lower than when I calculate the numbers on a spreadsheet. Just as an example I've attached the spreadsheet. If you look in the pivot table sheet in the spreadsheet, you'll see some formulas I use to calculate cancellation, reduction and new sales.

 

Sample spreadsheet

 

Kind regards,

Mike

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    Hi,

    My formula gets your desired result.  1 cancelled in Feb, i reduced cancellation in Feb and no new addition in Feb.  Where is the problem?

    • Mike282's avatar
      Mike282
      Icon for Helper III rankHelper III
      7 years ago

      Thanks Ashish_Mathur 

       

      Apologies it looks like it's working perfectly for the Invoice table! Could I ask another quick question. We realised that we need to run the same formula but based on a level down by a table called InvoiceItem table. The InvoiceItem table as a many to 1 relationship to the Invoice table by the InID. The InvoiceItem table contains a breakdown of the products within the one invoice located in the Invoice table.

       

      I want to essentially do the same calculation but completely from the InvoiceItem table instead. You can see in the attached PBIX file I've created a second matrix table visual to the right and it's built completely off the InvoiceItem table. I've tried several variations of your formula but I can't seem to get it working for a distinct count of the InID in the InvoiceItem table.

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        7 years ago

        Hi,

        You are welcome.  Before i answer the next question, please mark as Answer the particular post of mine which answered your first question.