Combo chart with calculation

[yuhkao]

Hi Expert

i need help for combining data and create combo chart. 

my raw data excel file contains two sheet. one is mutiple defect information, another is shipment with category and time.

i would like to create monthly combo chart in time as x-axis. column chart with total q'ty of defect in each month, line chart with failure rate[total defect/total shipment] in each month.

Sheet1

Category Date DefectType

Part1       201908    T1

Part2       201908    T1

Part3       201909    T2

Part4       201910    T1

Sheet2

Category  Date    Shipment

Part1       201908    1000

Part2       201908    500

Part3       201908    1000

Part4       201908    500

Part1       201909    1000

Part2       201909    500

Part3       201909    1000

Part4       201909    500

Part1       201910    1000

Part2       201910    500

Part3       201910    1000

Part4       201910    500

[Anonymous · Accepted solution]

Hi @yuhkao ,

Hope below is what you want:

Measure 2 = CALCULATE(SUM(Sheet2[Shipment]),FILTER(Sheet2,NOT(ISBLANK(LOOKUPVALUE(Sheet1[DefectType],Sheet1[Category],Sheet2[Category],Sheet1[Date],Sheet2[Date])))))

Measure 3 = DIVIDE([Measure 2],CALCULATE(SUM(Sheet2[Shipment])))

Aiolos Zhao

[Anonymous · Accepted solution]

Hi @yuhkao ,

Hope below is what you want:

Measure 2 = CALCULATE(SUM(Sheet2[Shipment]),FILTER(Sheet2,NOT(ISBLANK(LOOKUPVALUE(Sheet1[DefectType],Sheet1[Category],Sheet2[Category],Sheet1[Date],Sheet2[Date])))))

Measure 3 = DIVIDE([Measure 2],CALCULATE(SUM(Sheet2[Shipment])))

Aiolos Zhao

[yuhkao]

@Anonymous 

for measure 2, its not possible to input different table criteria after filter funtion. it shows gray text. 

[Anonymous]

@yuhkao 

Measure2 can be created in Sheet2 table like I said above with no errors, please try

Aiolos Zhao

[yuhkao]

@Anonymous  sorry for bother again. does original data must be table or sheet is okay? 

[Anonymous]

@yuhkao I think both are okay, the data source can be table, sheet, database I think.

Aiolos Zhao

[yuhkao]

@Anonymous 

sorry again, got stuck in another part.

As I create combo chart with date as shared axis, with matching two criteria of category and date as condition to build up the chart. 

line as for rate=[count of category]/[sum of shipment] 

column chart is count of defect type which is from sheet1

problem:

for the shared axis of combo chart, using sheet1 date or sheet2 date? i found one i select either of them., one of line or column data stays constant..... seems date is not share for both sheet1/sheet2 data.  how can i solve it? 

[Anonymous]

@yuhkao 

Yes, if there is no relationship between these two tables, the date won't be shared.

Please try to use the "manage relationship" in the "Modeling" tab to create the relationship between sheet1 and sheet2.

Aiolos Zhao

[yuhkao]

@Anonymous  what is measure 1 formula? still confused 

[Anonymous · Accepted solution]

Hi @yuhkao ,

Measure1 is not necessary for this chart I think, measure1 is :

Measure = CALCULATE(SUMX(Sheet1,LOOKUPVALUE(Sheet2[Shipment],Sheet2[Category],Sheet1[Category],Sheet2[Date],Sheet1[Date])))

Measure1 is created in the Sheet1 table,

Measure2 and Measure3 are created in the Sheet2 table.

Aiolos Zhao