Visual table cannot show correctly

[trinachung]

I have below sales & staff cost data and have created below measure to sum up the total staff cost a) staff costs with brand "All" are splitted into Brand A, B, C by "Sales Ratio by Brand", b) staff costs with Brand A, B, C are sum up by brands directly.  When I use the measure in the visual table, the data cannot be shown correctly.  Don't know if the measure is incorrect or the relatioship mapped incorrectly.

Staff Cost = calculate(sum('Individual Staff Expenses'[Total Staff Expenses by Individual]), FILTER('Individual Staff Expenses','Individual Staff Expenses'[Brand]="All"))*CALCULATE(sum('Sales Data'[Sales Ratio by Brand]),ALLEXCEPT('Sales Data','Sales Data'[Dept],'Sales Data'[Brand])) + calculate(sum('Individual Staff Expenses'[Total Staff Expenses by Individual]),filter('Individual Staff Expenses','Individual Staff Expenses'[Brand]<>"All"),ALLEXCEPT('Sales Data','Sales Data'[Dept]))

Sales Data

Total Staff Expense by Dept

Individual Staff Expenses

Individual Cost Ratio by Salary = 'Individual Staff Expenses'[Salary by Cost Split]/calculate(sum('Individual Staff Expenses'[Salary by Cost Split]),ALLEXCEPT('Individual Staff Expenses','Individual Staff Expenses'[Dept]))

Total Staff Expenses by Individual = related('Staff Expenses by Dept'[Total Staff Expenses])*'Individual Staff Expenses'[Individual Cost Ratio by Salary]

Relationship

Table

My expected result is using the master lists of "Brand" & "Dept" to show below table:

Dept	Brand A	Brand B	Brand C	Total
OPS A	568.18	1,931.82	0	2,500.00
OPS B	1,500.00	0	0	1,500.00
OPS C	0	702.00	1,098.00	1,800.00
SUP A	483.87	1,032.26	483.87	2,000.00
SUP B	241.94	516.13	241.94	1,000.00
Total	2,793.99	4,182.21	1,823.81	8,800.00

However, when I use the master list of "Brand" & "Dept", the data only show the total value.

When I use the Column "Brand" under Sales Data, it can only show the splitted cost of brand "All" correctly but not the total cost with different brands.

When I use the Column "Brand" under Individual Staff Expenses, it can only show the total cost with different brands correctly but not the splitted cost of brand "All".

[amitchandak]

@trinachung 

Dept should join to the Sales data Also.

The brand should to Individual staff exchanges

Staff cost in Brand can only be viewed by Brand.

Total Staff expense in the department can only be viewed by department 

I see the data modeling issue here.

Refer : https://docs.microsoft.com/en-us/power-bi/guidance/

[trinachung]

Thanks for your help, @amitchandak !!

I've tried to link the relationship again per your suggestion.  However, the table can only show this:

Is it possible to divide the cost of SUP A & SUP B by "Sales Ratio by Brand" into Brand A, B, C as well and shown in the same table just like below?

Dept	Brand A	Brand B	Brand C	Total
OPS A	568.18	1,931.82	0	2,500.00
OPS B	1,500.00	0	0	1,500.00
OPS C	0	702.00	1,098.00	1,800.00
SUP A	483.87	1,032.26	483.87	2,000.00
SUP B	241.94	516.13	241.94	1,000.00
Total	2,793.99	4,182.21	1,823.81	8,800.00

[amitchandak]

@trinachung , Can you share sample data in table format.

[trinachung]

@amitchandak Please find the data as below:

Sales Data

Dept	Brand	Sales
OPS A	Brand A	100
OPS A	Brand B	200
OPS B	Brand A	50
OPS C	Brand B	120
OPS C	Brand C	150

Total Staff Expenses

Dept	Total Staff Expenses
OPS A	2000
OPS B	1000
OPS C	2500
SUP A	1500
SUP B	1800

Individual Staff Expenses

Staff	Dept	Brand	Cost Split	Salary by Cost Split
Staff A	SUP A	All	1	500
Staff B	SUP A	All	1	200
Staff C	SUP A	All	1	300
Staff D	SUP B	All	1	200
Staff E	SUP B	All	1	300
Staff F	OPS A	Brand A	0.8	200
Staff F	OPS A	Brand B	0.2	50
Staff G	OPS A	Brand B	1	230
Staff H	OPS A	Brand B	1	400
Staff I	OPS B	Brand A	1	300
Staff J	OPS B	Brand A	1	400
Staff K	OPS C	Brand B	0.4	240
Staff K	OPS C	Brand C	0.6	360
Staff L	OPS C	Brand B	1	150
Staff M	OPS C	Brand C	1	250

Brand

Brand

Brand A

Brand B

Brand C

Dept

Dept

OPS A

OPS B

OPS C

SUP A

SUP B

[MFelix · Accepted solution]

Hi @trinachung ,

Keeping the originial relationship model you have you need to create the following measures:

Individual Cost Ratio by Salary =
SUM ( 'Individual Staff Expenses'[Salary by Cost Split] )
    / CALCULATE (
        SUM ( 'Individual Staff Expenses'[Salary by Cost Split] );
        ALLEXCEPT ( 'Individual Staff Expenses'; 'Individual Staff Expenses'[Dept] )
    )

Total Staff Expenses by Individual AUX =
IF (
    SELECTEDVALUE ( 'Individual Staff Expenses'[Brand] ) = "All";
    SUM ( 'Total Staff Expenses'[Total Staff Expenses] )
        * CALCULATE (
            [% sales];
            FILTER (
                ALL ( 'Sales Data'[Brand] );
                'Sales Data'[Brand] = SELECTEDVALUE ( Brand[Brand] )
            )
        );
    CALCULATE (
        SUM ( 'Total Staff Expenses'[Total Staff Expenses] ) * [Individual Cost Ratio by Salary];
        FILTER (
            'Individual Staff Expenses';
            'Individual Staff Expenses'[Brand] = SELECTEDVALUE ( Brand[Brand] )
        )
    )
) + 0


% sales = SUM('Sales Data'[Sales])/CALCULATE(SUM('Sales Data'[Sales]);ALL('Sales Data'))

Total Staff Expenses by Individual =
IF (
    HASONEVALUE ( Dept[Dept] );
    SUMX ( Brand; [Total Staff Expenses by Individual AUX] );
    IF (
        HASONEVALUE ( Brand[Brand] );
        SUMX ( Dept; [Total Staff Expenses by Individual AUX] );
        SUM ( 'Total Staff Expenses'[Total Staff Expenses] )
    )
)

Now just setup you table as needed.

Your issue was related with the ALL part of the split by brand of the department costs, so you need to force for those the split by % of sales.

As you can see in the file I have attach I have no additional calculated columns all measures.

Check the result in attach PBIX file.

[trinachung]

Hi @MFelix,

One more question, if Staff A's expense is splitted into 2 rows, like below:

Staff	Dept	Brand	Product Type	Cost Split	Salary by Cost Split
Staff A	SUP A	All	Type A	0.7	350
Staff A	SUP A	Brand A	Type B	0.3	150

Staff A is working for both brand All and Brand A but is under SUP A dept.  How could the measures work?

[MFelix]

Hi @trinachung ,

How does this impact the numbers you have? Will the sup A only consider 70%of the 2K

[trinachung]

Hi @MFelix 

If I have to split the cost with product type as well with below data (Only change the cost splits of Staff A & Staff J, others are the same), how could the measures "Total Staff Expenses by Individual" works?

For example, Staff A is under "SUP A" department, his cost splits are including both brand "All" & "Brand A".

Individual Staff Expenses

Staff	Dept	Brand	Product Type	Cost Split	Salary by Cost Split
Staff A	SUP A	All	Type All	0.7	350
Staff A	SUP A	Brand A	Type A	0.3	150
Staff B	SUP A	All	Type All	1	200
Staff C	SUP A	All	Type All	1	300
Staff D	SUP B	All	Type All	1	200
Staff E	SUP B	All	Type All	1	300
Staff F	OPS A	Brand A	Type A	0.8	200
Staff F	OPS A	Brand B	Type A	0.2	50
Staff G	OPS A	Brand B	Type B	1	230
Staff H	OPS A	Brand B	Type B	1	400
Staff I	OPS B	Brand A	Type A	1	300
Staff J	OPS B	All	Type All	0.6	240
Staff J	OPS B	Brand A	Type B	0.4	160
Staff K	OPS C	Brand B	Type A	0.4	240
Staff K	OPS C	Brand C	Type B	0.6	360
Staff L	OPS C	Brand B	Type B	1	150
Staff M	OPS C	Brand C	Type C	1	250

Expected table would be:

Dept	Brand A	Brand B	Brand C	Total
OPS A	568.18	1,931.82	0	2,500.00
OPS B	1,110.13	265.44	124.43	1,500.00
OPS C	0	702.00	1,098.00	1,800.00
SUP A	711.29	877.42	411.29	2,000.00
SUP B	241.94	516.13	241.94	1,000.00
Total	2,793.99	4,182.21	1,823.81	8,800.00

[trinachung]

Hi, anyone could help?  Thanks!!

[trinachung]

Great thanks for the solution, @MFelix !!