Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |