Create a Calculated Column from Different Tables into a measure

[Anonymous]

Hi, 

I have three excel / power bi table sheets 

1. X

2. Y 

3.  Z

in sheet X I have values which are from department "a" and has classification as "A" "B" "C" "D" for various things

in sheet Y aslo I have values which are from department "a" has classication as "A", "B","C","D" for various things

Simlarly in sheet Y I have values from depratment "b" and in sheet z values from department "b" with similar classifications 

I want to add Class A and B for each department separately. Then I have to chart the values monthly for each depatment from all the three sheets together. 

Please let me know how can I calculate values for department a from sheets x&y and values from department b from sheets y&z. I need help in segragating the data. 

Please find the attached pictures to get an idea about the data.

Note: in Sheet Y data depatment is divided by "Day" and "Swing"

Thank you.

[v-juanli-msft · Accepted solution]

Hi @Anonymous

Based on my understanding, your requirement is to:

count Class A and B in these three sheets for each department, finally show the count monthly.

Right?

I make a test as below:

In Queries Editor, create a “new Query”,

New source->blank query

Then open Advanced editor, write code

let

    Source1 =Table.SelectColumns(Sheet1,{"date","from","class"}),

    Source2 =Table.SelectColumns(Sheet2,{"date","from","class"}),

    Source3 =Table.SelectColumns(Sheet3,{"date","from","class"}),

    #"Appended Query" = Table.Combine({Source1, Source2, Source3})

in

#"Appended Query"

Then split column

Then close &&apply,

Create a calendar date table, using formula after clicking on “New Tbale”,

calendar date = CALENDARAUTO()

create relationship between “calendar table” and “new query”

create a measure in your table “new Query”,

Measure =
CALCULATE (
 COUNT ( 'new Query'[class] ),
 FILTER (
 'new Query',
 'new Query'[from.1] = MAX ( 'new Query'[from.1] )
 && ( 'new Query'[class] = "A"
 || 'new Query'[class] = "B" )
 )
)

then add “date” in “calendar table” in the Axis, add measure in “Value”, add “from.1” in the “Legend”.

Best Regards

Maggie