Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
Proud to be a Super User!
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
Proud to be a Super User!
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
Proud to be a Super User!
for measure 2, its not possible to input different table criteria after filter funtion. it shows gray text.
Measure2 can be created in Sheet2 table like I said above with no errors, please try
Aiolos Zhao
Proud to be a Super User!
@AiolosZhao sorry for bother again. does original data must be table or sheet is okay?
@yuhkao I think both are okay, the data source can be table, sheet, database I think.
Aiolos Zhao
Proud to be a Super User!
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?
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
Proud to be a Super User!
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
Proud to be a Super User!