Column with different logic/conditions in two measures

[Anonymous]

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!

[v-juanli-msft]

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]

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

[Anonymous]

Hi @v-juanli-msft

Any update on this?

Regards,

Sai

[v-juanli-msft · Accepted solution]

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

[Anonymous]

Hi @v-juanli-msft

Thanks Maggie. That solution worked for my requirement.
I do have a follow up requirement on this. I did post the question in the community. Can you please take a look into it and let me know if there is any solution to achieve it.

http://community.powerbi.com/t5/Desktop/Dynamic-Rolling-Sum-4-Quarters-Trailing/m-p/416479#M191264

Thanks & Regards,
Sai

[Anonymous]

Hi @v-juanli-msft

Any update on the http://community.powerbi.com/t5/Desktop/Dynamic-Rolling-Sum-4-Quarters-Trailing/m-p/416479#M191264

Thanks & Regards,
Sai