Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Create a Calculated Column from Different Tables into a measure

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.

 

 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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

     

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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