Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
trinachung
Helper I
Helper I

Visual table cannot show correctly

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

Sales Data.PNG

Total Staff Expense by Dept

Staff Expenses by Dept.PNG

Individual Staff Expenses

Staff Expenses by Individuals.PNG

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

Relationship.PNG

Table

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

DeptBrand ABrand BBrand CTotal
OPS A     568.18  1,931.820  2,500.00
OPS B  1,500.0000  1,500.00
OPS C0     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.

Table 1.PNG

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.

Table 2.PNG

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".

Table 3.PNG

1 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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

10 REPLIES 10
amitchandak
Super User
Super User

@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/

 

Thanks for your help, @amitchandak !!

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

Table.PNG

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?

DeptBrand ABrand BBrand CTotal
OPS A     568.18  1,931.820  2,500.00
OPS B  1,500.0000  1,500.00
OPS C0     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

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

@amitchandak Please find the data as below:

 

Sales Data

DeptBrandSales
OPS ABrand A100
OPS ABrand B200
OPS BBrand A50
OPS CBrand B120
OPS CBrand C150

 

Total Staff Expenses

DeptTotal Staff Expenses
OPS A2000
OPS B1000
OPS C2500
SUP A1500
SUP B1800

 

Individual Staff Expenses

StaffDeptBrandCost SplitSalary by Cost Split
Staff ASUP AAll1500
Staff BSUP AAll1200
Staff CSUP AAll1300
Staff DSUP BAll1200
Staff ESUP BAll1300
Staff FOPS ABrand A0.8200
Staff FOPS ABrand B0.250
Staff GOPS ABrand B1230
Staff HOPS ABrand B1400
Staff IOPS BBrand A1300
Staff JOPS BBrand A1400

Staff K

OPS CBrand B0.4240
Staff KOPS CBrand C0.6

360

Staff LOPS CBrand B1150
Staff MOPS CBrand C1250

 

Brand

Brand
Brand A
Brand B
Brand C

 

Dept

Dept
OPS A
OPS B
OPS C
SUP A
SUP B

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix,

 

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

 

StaffDeptBrandProduct TypeCost SplitSalary by Cost Split
Staff ASUP AAllType A0.7350
Staff ASUP ABrand AType B0.3150

 

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

Hi @trinachung ,

 

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

StaffDeptBrandProduct TypeCost SplitSalary by Cost Split
Staff ASUP AAllType All0.7350
Staff ASUP ABrand AType A0.3150
Staff BSUP AAllType All1200
Staff CSUP AAllType All1300
Staff DSUP BAllType All 1200
Staff ESUP BAllType All1300
Staff FOPS ABrand AType A0.8200
Staff FOPS ABrand BType A0.250
Staff GOPS ABrand BType B1230
Staff HOPS ABrand BType B1400
Staff IOPS BBrand AType A1300
Staff JOPS BAllType All0.6240
Staff JOPS BBrand AType B0.4160

Staff K

OPS CBrand BType A0.4240
Staff KOPS CBrand CType B0.6

360

Staff LOPS CBrand BType B1150
Staff MOPS CBrand CType C1250

 

Expected table would be:

 

DeptBrand ABrand BBrand CTotal
OPS A568.18  1,931.820  2,500.00
OPS B1,110.13265.44124.43  1,500.00
OPS C0     702.00  1,098.00  1,800.00
SUP A711.29877.42411.29  2,000.00
SUP B241.94516.13241.94  1,000.00
Total2,793.99  4,182.21  1,823.81  8,800.00

Hi, anyone could help?  Thanks!!

Great thanks for the solution, @MFelix !!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.