locked
Calculated Field - % of value from another table RRS feed

  • Question

  • Hello,

    I am trying to find the percentage of lost revenue based on last quarter's ending revenue.

    I have two tables; a transaction table that has all lost revenue. This is a historical table so it has what we call "ReportedQ" for the quarter that it occurs in. The second table is a grouped table of historical quarterly end of period revenue. It simply has Site, ReportedQ, and Total Revenue.

    I've created a relationship from the Transaction table to the EndofPeriodRev table on a concatenation between ReportedQ and Site. In the pivot table I'm working with, I've filtered by just lost transactions and I've got Transaction metrics by site. I'm trying to add in a calculated column to show the Transaction revenue (lost revenue) over the total revenue from the related table Total Revenue. My attempt looked like the following: 

    =CALCULATE(sum(Transaction[Revenue]),Transaction[TransType] = "Lost")/max(EndofPeriodRev[TotalRevenue])

    Except the above appears to be pulling in max TotalRevenue regardless of the relationship that was made. I even tried to pull in TotalRevenue to the Transaction table via RELATED() as a calc'd column. The formula above thus becomes:

    =CALCULATE(sum(Transaction[Revenue]),Transaction[TransType] = "Lost")/max(Transaction[TotalRevenue])

    The above works at each individual site level, but the total % is off. From what I can tell, it's only using the sites that have data to pull in the denominator. Which means that my total % lost revenue is only including the sites shown, and not the total portfolio. I want the full amount of EndofPeriodRev[TotalRevenue] for the quarter to be used in that grand total line, inclusive of sites without any lost revenue. Please help. If possible, with just a calculated field and not a calculated column. Simply to see how I can appropriately take advantage of defined relationships in future calc'd fields.

    Thank you.


    - Addison

    Monday, April 3, 2017 3:54 PM

Answers

  • Hello,

    In your pivot table bring  Site as row headers and ReportedQ as slicer from EndOfPeriodRev table

    Here is the measure

    LostSalesRatio :=
    DIVIDE (
        CALCULATE (
            SUMX ( RELATEDTABLE ( 'Transaction' ), 'Transaction'[SalesAmount] ),
            'Transaction'[Type] = "Lost"
        ),
        SUM ( EndOfPeriodRev[TotalRevenue] )
    )







    • Marked as answer by Madadd33 Tuesday, April 4, 2017 10:22 PM
    Tuesday, April 4, 2017 4:15 PM

All replies

  • Hello, 

    Since you have normalized your model with RELATED () and now got all the fields needed for calculation in one table, you can try the solution below. 

    =
    DIVIDE (
        CALCULATE ( SUM ( Transactions[Revenue] ), Transactions[Type] = "Lost" ),
        CALCULATE ( SUM ( Transactions[TotalRevenue (RELATED)] ), ALL ( Transactions ) )
    )
    

    You also have an option to nest RELATED() into the DAX code above if you prefer to work with your denormalized model. 



    Thanks, 
    Nick - 

    Monday, April 3, 2017 9:41 PM
  • Hello Nick,

    Thank you for the response, but I think there's a misunderstanding. I'm looking for a calculated field not column for use in a pivot table. The pivot table in question has a bunch of "Won" sales stats and then also this Lost% against the period total. To hopefully help clarify, below is a link to an example. You can see the relationship defined in the data model and the calculated fields as they stand right now.

    Example Lost Percent

    Thanks



    - Addison

    Tuesday, April 4, 2017 12:17 AM
  • Hello,

    In your pivot table bring  Site as row headers and ReportedQ as slicer from EndOfPeriodRev table

    Here is the measure

    LostSalesRatio :=
    DIVIDE (
        CALCULATE (
            SUMX ( RELATEDTABLE ( 'Transaction' ), 'Transaction'[SalesAmount] ),
            'Transaction'[Type] = "Lost"
        ),
        SUM ( EndOfPeriodRev[TotalRevenue] )
    )







    • Marked as answer by Madadd33 Tuesday, April 4, 2017 10:22 PM
    Tuesday, April 4, 2017 4:15 PM
  • Nick,

    Awesome! Using the Site and ReportedQ slicers from the EndofPeriodRev table were the big pieces I was missing. 

    My only follow up is why? If the tables have a defined relationship on a concatenation of site+ReportedQ, why then would it matter which site/ReportedQ field you used?


    - Addison

    Tuesday, April 4, 2017 7:21 PM
  • Hello again,

    The answer for why lies in cardinality of the data model. With grouped concatenation over two columns you created a one to many relationship (EndofPeriodRev - one side and Transactions - many side, we also call these dimensions and facts . Except,  your data is really a two fact table model). First of all you always want to use attributes for filtering from your one side or dimension, it's a rule in our world. Secondly, in this particular case your model did not have all the one side keys for Site on the many side but at the same time for the denominator you wanted all the aggregated sales totals living on one side, so there is no other way except pulling the sales records filtered as "lost" from many side and dividing them by the totals form one side and then letting the one side filter context (Site) do the rest. 

    Basically, the lesson to learn here is that we always want to filter by the one side. 

    Hopefully that was helpful.  Reading some textbooks on dimensional modelling will help you to understand more how this stuff works. 

    By the way, if I answered your question, please mark it as answered

    Thanks, Nick - 

    Tuesday, April 4, 2017 8:17 PM
  • Thank you Nick!

    That's helpful. I plan to take more structured classes as I'm sure most of the issues I'm running into are because I'm trying to self teach. I assume wrongly it works like old excel and/or SQL in defining and using relational information. I understand that it's wrong to have the same information in two table (ReportedQ and Site), but it was only to create my key. Otherwise that's sound advice, Always use attributes from the one side.

    Thanks again.


    - Addison

    Tuesday, April 4, 2017 10:33 PM