How to compute Table C base on the highest value from Table A and B ?

[admin11]

Hi All

My sales Table , each customer sales order can be contribute by 1 or 2 different sales group CLASS or TYPE.

I am able to create report Table A and B by sales staff.  no issue as below. 

Sales table by (CLASS) sales staff base on customer base allocate to them :-

Table A 

GROUP_CLASS_K	Sales LYTD
AO	41760
AP	34163
Other	25437
Total	101360

Sales report by (TYPE) sales staff , base on customer allocate to them and also include those customer sales from other sales staff :-

Table B

GROUP_TYPE_K	Sales LYTD
AO	71660
AP	4263
Other	25437
Total	101360

May i know how to create the Table ,  As below :-

Table C ( Expected Result )

GROUP SALES	Sales LYTD
AO	71660
AP	34163
Other	25437
Total	131260

Please note that , above table table the highest value from Table A and B. 

Above Table Row 1 amount 71660 , taken from Table B

Above Table Row 2 amount 34163 , taken from Table A

Remark :- 

Table A Column 1 field name = GROUP_CLASS_K

Table B Column 1 field name = GROUP_TYPE_K

Hope some can advise me how to achieve the above ?

Paul Yeo

[Ashish_Mathur]

Hi,

Standardise the column headings of TableA and TableB.  Create another Table with a single heading called Group.  Create a relationship from the the standardised heading of the appende Table to the Group column of the single column table.  to your visual, drag the heading from the single column table.  Write these measures:

Measure = max(data[Sales LYTD])

Measure1 = if(hasonevalue(Groups[Group]),[Measure],SUMX(VALUES(Groups[Group]),[Measure]))

Hope this helps.

[admin11]

@Ashish_Mathur 

I am not sure what you mean , standarise the column heading ?

Enclosed my PBI file  , hope you can advise me.

https://www.dropbox.com/s/774pcqhxaedsjul/PBT_V2021_515%20ASK_.pbix?dl=0

Paul

[Ashish_Mathur]

By that i mean ensure that the spellings of the headings are the same.  Please do it yourself.

[admin11]

@Ashish_Mathur 

Thank you very much for your advise. 

i have create a new table as below :-

When i try to create the measure you mentioned :-

Measure_ASHISH = max('Date'[Sales LYTD])

I get error msg :-

Column 'Sales LYTD' in table 'Date' cannot be found or may not be used in this expression.

Can you please advise me where go wrong ? 

My PBI file :-

https://www.dropbox.com/s/20swme1z1vvss90/PBT_V2021_515%20ASK_1.pbix?dl=0

Paul

[Ashish_Mathur]

Hi,

You did not mention that Group Type and Group Class are calculated tables.  I do not work with calculated tables.  Someone else will help you.

[admin11]

@Ashish_Mathur 

When you said my table is calculated table. Expression can not be created. 

i Try to change to below :-

Measure_ash = max('SALES'[SALES_])

Also cannot..

May i know , what should i modify my table , so that your expression , will be no error ?

Paul