Are we referring more for Service A exp or Service B exp a proportion of Total Service Expense

[aiyosap]

Dear all,

I have the following question and hope you can help. 

I have four tables ( Management_Service_Charge Table,  Service_A table, Service_B table, and Time Intelligent table).

The relationship among the table are:
* Management_Service_Charge table  (ONE) to Service_A table (Many) with Order ID

* Management_Service_Charge Table (ONE) to Service_B table (Manay) with Order ID

* TimeIntelligent Table (ONE) to Management_Service_Charge table (Many) with Order Date

* TimeIntelligent Table (ONE) to Service_A table (Many) with Order Date
* TimeIntelligent Table (ONE) to Service_B table (Many) with Order Date

About the tables:
* Service_A table consists of individual request order records with order amount ($)

* Service_B table consists of individual request order records with order amount ($)

* Management_Service_Charge table consists of individual request order records with Manage_Service_Fees amount ($)

* All services order (either service A or service B) provied by this vendor will incur a management service charge

Total Service Request amount = (Order amount of Service order A + Management Service)  +

                                                    (Order amount of Service order B + Management Service)

Objective: I would like to achieve the insight to find out which service is more expensive (i.e. if Service_A or Service_B are more expensive as a proportion of the total service request breaking down by each month).

Questions: 

1. How to show this in a barchart below?
   
2. What kinds of measure objects I need to create? and what DAX formulat to use for those Measurement?

Regards,

Aiyosap

[v-rzhou-msft · Accepted solution]

Hi @aiyosap 

I build a sample to have a test. 

Management_Service_Charge:

Service A:

Service B:

TimeIntelligent is a calculated date table.

TimeIntelligent =
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "ShortMonth", FORMAT ( [Date], "MMM" ),
    "YearMonth",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] )
)

 Relationship:

Due to you build relationships between Management_Service_Charge table and TimeIntelligent Table. We can see that relationships between TimeIntelligent Table and Service A/B are inactive.

Measures:

Service A = 
VAR _Manage_Amount_A =
    CALCULATE (
        SUM ( Management_Service_Charge[Manage_Service_Fees amount] ),
        FILTER (
            'Management_Service_Charge',
            Management_Service_Charge[Service Type] = "A"
        )
    )
VAR _Order_Amount_A =
    CALCULATE (
        SUM ( 'Service A'[Order amount] ),
        USERELATIONSHIP ( TimeIntelligent[Date], 'Service A'[Order Date] )
    )
VAR _ServiceA_Order = _Manage_Amount_A + _Order_Amount_A
RETURN
    DIVIDE ( _ServiceA_Order, [Total Service Request Order] )

Service B = 
VAR _Manage_Amount_B =
    CALCULATE (
        SUM ( Management_Service_Charge[Manage_Service_Fees amount] ),
        FILTER (
            'Management_Service_Charge',
            Management_Service_Charge[Service Type] = "B"
        )
    )
VAR _Order_Amount_B =
    CALCULATE (
        SUM ( 'Service B'[Order amount] ),
        USERELATIONSHIP ( TimeIntelligent[Date], 'Service B'[Order Date] )
    )
VAR _ServiceB_Order = _Manage_Amount_B + _Order_Amount_B
RETURN
    DIVIDE ( _ServiceB_Order, [Total Service Request Order] )

Total Service Request Order = 
VAR _Manage_Amount =
    SUM ( Management_Service_Charge[Manage_Service_Fees amount] )
VAR _Order_Amount_A =
    CALCULATE (
        SUM ( 'Service A'[Order amount] ),
        USERELATIONSHIP ( TimeIntelligent[Date], 'Service A'[Order Date] )
    )
VAR _Order_Amount_B =
    CALCULATE (
        SUM ( 'Service B'[Order amount] ),
        USERELATIONSHIP ( TimeIntelligent[Date], 'Service B'[Order Date] )
    )
RETURN
    _Manage_Amount + _Order_Amount_A + _Order_Amount_B

Result is as below.

We can see that in cluster column chart Percentage type columns and Whole number type column will use the same Y axis. So Percentage type columns are not obvious due to their small values. I suggest you to create an other column chart to show Percentage value from Service A and Service B.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.