Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Column with different logic/conditions in two measures

REVENUE BY SEGMENT

RevenueSegment = IF(Table[FINANCIAL_SEGMENT] IN {"Market","Channel"}, "Marketing",

IF(Table[FINANCIAL_SEGMENT] IN {"Business","Other","Self-Service","MB",  "SOHO"},"Business", Table[FINANCIAL_SEGMENT])))


Total Revenue = SUM(Table[Revenue])

CUSTOMER COUNT

 CustomerCount Segment =

        IF(Table[FINANCIAL_SEGMENT] IN {"Market","Channel"}
 && Table[HYPERION_DEPARTMENT] IN {"Field Consulting","Sales General - Other","Sales General","Sales General - Top Accounts","Department"}, "Marketing",

IF(Table[FINANCIAL_SEGMENT] IN {"Business","MB","Self-Service","Unassigned"}  && Table[HYPERION_DEPARTMENT] IN {"Field Consulting","Sales General - Other","Sales General","Sales General - Top Accounts","Department"}, "Business")))


CustomerCount LastMonth Qtr =

CALCULATE (  DISTINCTCOUNT(Table[PARENT_ID]),DATESINPERIOD ( Table[Month], ENDOFQUARTER( Table[Month] ), -1, Month))

 

 

TBD OUTPUT

Formula = (REVENUE BY SEGMENT/ CUSTOMER COUNT)

 

 

The segment definitions are different for both the measures and I am also trying to show both the measures in a single matrix by segment.

Can anyone assist me? Many thanks in advance!

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    Hi 

    After struggling with this problem, I find we should concern about the context row when using a measure.

    In the third matrix, it will aggregate the measure based on the columns added to columns and rows fields.

    I could only figure out a similar result as you wanted, by drilling at the lowest level of data on matrix and put some filters to the matrix.

     

    here is pbix

     

    Best Regards

    Maggie

6 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    8 years ago
    Hi Anonymous
    Since the segment definitions are different for both the measures, are the values (which is Total Revenue/ CustomerCount LastMonth Qtr) for the Value Field in the third matrix TBD OUTPUT affected by different the segment definitions?
    “I am also trying to show both the measures in a single matrix by segment.” Does this mean to show RevenueSegment and CustomerCount Segment separately in a single matrix?
    It would be much appreciated for you to share a pbix with me.
     
    Best Regards
    Maggie
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi v-juanli-msft

      “Since the segment definitions are different for both the measures, are the values (which is Total Revenue/ CustomerCount LastMonth Qtr) for the Value Field in the third matrix TBD OUTPUT affected by different the segment definitions?”
      - Yes. That is correct.

      “I am also trying to show both the measures in a single matrix by segment.” Does this mean to show RevenueSegment and CustomerCount Segment separately in a single matrix?
      - No. I'm trying to show single/common segment in a matrix (not Revenue Segment and CustomerCount segment seperately)


      Please let me know if you have any other questions.

      pbix file