pivot

12 Topics

Issue with pivoting via DAX
Hello, This is piggy-backing off of a previous post I had made and I continue to struggle which is my fault because my real data has more columns of interest that I did not share (apologies for that). I have this fake data here BUT it is important to note that my real data has many more students than just #1-4. (It also has many more columns but they are not of interest). StudentID Level Test Score Category 1 High School Math 88 Above Average 1 Middle School English 90 Above Average 1 High School Science 83 Average 1 Middle School Science 89 Above Average 1 High School Social Studies 97 Above Average 1 Middle School Social Studies 88 Above Average 2 High School English 95 Above Average 2 Middle School English 90 Above Average 2 High School Social Studies 68 Below Average 2 Middle School Social Studies 78 Average 3 High School Math 85 Average 3 Middle School Math 72 Average 3 High School English 93 Above Average 3 Middle School English 90 Above Average 3 High School Science 86 Above Average 3 Middle School Science 95 Above Average 4 High School English 93 Above Average 4 Middle School English 89 Above Average 4 High School Science 81 Average 4 Middle School Science 87 Above Average 4 High School Social Studies 74 Average 4 Middle School Social Studies 70 Average I want each studentID to have only 1 row for high school, 1 row for middle school, and the subjects to be in columns as in the table below. I have pivoted every way I can, and I have tried the DAX solution in my original post suggested by the very kind & helpful v-dineshya but I am not having success. Is there a way to achieve this? StudentID Level Math Category English Category Science Category Social Studies Category 1 High School 88 Above Average null null 83 Average 97 Above Average 1 Middle School null null 90 Above Average 89 Above Average 88 Above Average 2 High School null null 95 Above Average null null 68 Below Average 2 Middle School null null 90 Above Average null null 78 Average 3 High School 85 Above Average 93 Average 86 Above Average null null 3 Middle School 72 Average 90 Above Average 95 Above Average null null 4 High School null null 93 Above Average 81 Average 74 Average 4 Middle School null null 89 Above Average 87 Above Average 70 Average Thank you in advance!
Solved
afaherty
9 months ago Place DAX Commands and Tips
1.7KViews
0likes
9Comments
Totals in pivot table excluding certain rows under condition
Hello, I would like to create a DAX measure which will calculate booking value as sum of ending backlog + sum of shipments - sum of beginning backlog, but only in case there will values of beginning and ending backlog in pivot table. In case there will be only beginning backlog value, then the booking will be 0 or blank. Total value of booking column will be calculated in the same logic - so in case the ending backlog of certain row will be missing, then the whole row will be calculated as 0. See the printscreen with final expected result I need in Power BI visualisation: I enclose dataset below as well. Thank you very much in advance. Best regards, Tomas BeginningBacklog EndingBacklog Fiscal_Year Fiscal_Week_Num Source PROD_LINE Coverage$ ITEM_NUMBER WAREHOUSE CUSTOMER_NAME FY-FW 5/4/2024 5/11/2024 2024 19 Shipments 1759 - OP EMEA C5 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1759 47 U1 EMEA C4 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1759 - T EMEA C3 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1759 1,907 T EMEA C3 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1759 715 S EMEA C3 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1759 1,192 S EMEA C3 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1759 1,143 OP EMEA C5 2024-19 5/4/2024 5/11/2024 2024 19 Beginning Backlog 1759 8,453 U1 EMEA C4 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1759 - S EMEA C3 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1759 7,430 OP EMEA C5 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1759 3,242 OP EMEA C5 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1759 339 U2 EMEA C4 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1759 408 U2 EMEA C4 2024-19 5/4/2024 5/11/2024 2024 19 Ending Backlog 1759 417 OP EMEA C5 2024-19 5/4/2024 5/11/2024 2024 19 Ending Backlog 1759 7,295 T EMEA C3 2024-19 5/4/2024 5/11/2024 2024 19 Ending Backlog 1759 8,988 OP EMEA C5 2024-19 5/4/2024 5/11/2024 2024 19 Ending Backlog 1760 1,928 Q APAC C2 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1753 17,697 U3 APAC C4 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1753 332 U3 APAC C4 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1753 415 OP APAC C5 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1753 27,802 Q APAC C2 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1753 - OP KOREA C5 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1753 16,823 U1 APAC C4 2024-19 5/4/2024 5/11/2024 2024 19 Beginning Backlog 1752 497 OP EU C5 2024-19 5/4/2024 5/11/2024 2024 19 Beginning Backlog 1752 297 OP EU C5 2024-19 5/4/2024 5/11/2024 2024 19 Beginning Backlog 1752 276 A EU C1 2024-19 5/4/2024 5/11/2024 2024 19 Beginning Backlog 1760 7,815 Q KOREA C2 2024-19 5/4/2024 5/11/2024 2024 19 Beginning Backlog 1759 1,790 Q KOREA C2 2024-19 5/4/2024 5/11/2024 2024 19 Beginning Backlog 1759 6,587 EE KOREA C2 2024-19 5/4/2024 5/11/2024 2024 19 Beginning Backlog 1753 8,881 EE KOREA C2 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1753 15,431 EE KOREA C2 2024-19 5/11/2024 5/18/2024 2024 20 Beginning Backlog 1752 2,017 OP EU C5 2024-20 5/11/2024 5/18/2024 2024 20 Beginning Backlog 1752 380 T EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Beginning Backlog 1752 1,190 T EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Beginning Backlog 1752 669 T EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Beginning Backlog 1752 551 S EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Beginning Backlog 1752 950 A EU C1 2024-20 5/11/2024 5/18/2024 2024 20 Beginning Backlog 1752 69 B EU C1 2024-20 5/11/2024 5/18/2024 2024 20 Beginning Backlog 1752 159 S EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Beginning Backlog 1752 8 S EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Beginning Backlog 1752 1,078 S EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 950 A EU C1 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 69 A EU C1 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 380 T EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 159 T EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 669 T EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 8 S EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 1,078 S EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 1,190 S EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 551 S EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 1,848 EE EU C2 2024-20 5/11/2024 5/18/2024 2024 20 Shipments 1752 1,319 A EU C1 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 430 S EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 12 S EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 1,274 S EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 734 S EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 1,353 EE EU C2 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 1,058 U2 EU C4 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 1,331 U1 EU C4 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 1,383 A EU C1 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 1,266 B EU C1 2024-20 5/18/2024 5/25/2024 2024 21 Beginning Backlog 1752 380 S EU C3 2024-21 5/18/2024 5/25/2024 2024 21 Beginning Backlog 1752 1,190 S EU C3 2024-21 5/18/2024 5/25/2024 2024 21 Beginning Backlog 1752 669 S EU C3 2024-21 5/18/2024 5/25/2024 2024 21 Beginning Backlog 1752 551 S EU C3 2024-21 5/18/2024 5/25/2024 2024 21 Beginning Backlog 1752 950 B EU C1 2024-21 5/18/2024 5/25/2024 2024 21 Beginning Backlog 1752 69 B EU C1 2024-21 5/18/2024 5/25/2024 2024 21 Beginning Backlog 1752 159 S EU C3 2024-21 5/18/2024 5/25/2024 2024 21 Beginning Backlog 1752 8 S EU C3 2024-21 5/18/2024 5/25/2024 2024 21 Beginning Backlog 1752 1,078 S EU C3 2024-21 5/18/2024 5/25/2024 2024 21 Beginning Backlog 1752 1,319 B EU C1 2024-21 5/18/2024 5/25/2024 2024 21 Beginning Backlog 1752 12 S EU C3 2024-21 5/18/2024 5/25/2024 2024 21 Beginning Backlog 1752 430 S EU C3 2024-21 5/18/2024 5/25/2024 2024 21 Beginning Backlog 1752 1,848 EE EU C2 2024-21 5/18/2024 5/25/2024 2024 21 Shipments 1752 1,383 B EU C1 2024-21
Solved
tomasjezek
2 years ago Place DAX Commands and Tips
493Views
0likes
1Comment
Combining two tables in a pivot chart / calculating SUM table 2 based on row label of table 1
Hey everybody, I assume this shouldn't be a big deal but I've already spend hours on this and I'm not able to figure it out on my own. I've two tables which I'd like to combine in a pivot chart, including claculations between both tables. I'd like to have a pivot chart, showing: x-axis: products (working ✔️) y-axis (main): Distinct count of serial numbers of serviced products (working ✔️) y-axis (secondary): Values on x-axis (main) in relation to build products (not working ❌) The example file can be found here: example_combine_two_tables_in_pivot_chart.xlsx Input data (the two tables) - worksheet "Input_data" table "tbl_build_products" - which products where build in which year and in which amount (multiple entries, not aggregated by year / product) Product build year amount of products build A 2020 1 B 2020 1 C 2020 2 A 2020 2 B 2020 3 C 2021 3 A 2021 4 B 2021 4 C 2021 5 A 2021 5 table "tbl_serviced_products" - which products where serviced when (multiple entries per product and serial number possible) Product serial number service year A S1 2020 B S2 2020 C S3 2020 A S1 2021 B S2 2021 C S6 2021 A S7 2020 B S8 2020 C S9 2020 A S10 2020 B S2 2021 C S12 2021 A S13 2021 B S14 2021 C S15 2021 A S16 2020 B S17 2020 C S18 2021 current data model I've marked the columns which contain identical values. How it looks like at the moment - worksheet "Pivot_table_chart" blue bars = Distinct count of serial numbers of serviced products (working ✔️) orange bars = Instead, here I'd like to show the values on x-axis (main) in relation to build products (not working ❌). Currently, the orange bars are representing the sum of build products in total, not according to the corresponding row labels. Underlying, there's a measure which is currently summing column "amount of products build" in table "tbl_build_products". This measure looks like this: =SUMX(VALUES(tbl_build_products[amount of products build]);[Sum of amount of products build]) What I'm trying to achieve (next steps) Change measure in a way that I'm getting the sum of column "amount of products build" in table "tbl_build_products" but using the row labels as a kind of filter Calculate the percentage of "Distinct Count of serial number" in relation to all build products of the same product (see Nr. 1) Instead of the orange bars, show the percentage value of Nr. 2 as a line diagram on the secondary y-axis I'm open to other ideas to achieve my goal. I'm looking forward to your assistance! Thanks 🙂 edit I've created another table where I've used regular excel formulas to show what I'm trying to achieve in the end. My problem is that the real dataset is more complicated and the helper column "helper_column_count_distinct" I've inserted into "tbl_serviced_products" (worksheet Input_data) only works as long as all entries are valid. As soon as I start filtering those entries in a pivot chart using another column, the values in the helper column wouldn't be correct anymore. That's why I'm trying to make it work in a way where I can use the full power of a pivot chart. Here is the updated example file: example_combine_two_tables_in_pivot_chart_2.xlsx Input tbl_service_products with helper column Product serial number service year helper_column_count_distinct A S1 2020 0,5 B S2 2020 0,3 C S3 2020 1 A S1 2021 0,5 B S2 2021 0,3 C S6 2021 1 A S7 2020 1 B S8 2020 1 C S9 2020 1 A S10 2020 1 B S2 2021 0,3 C S12 2021 1 A S13 2021 1 B S14 2021 1 C S15 2021 1 A S16 2020 1 B S17 2020 1 C S18 2021 1 Mockup end result using regular excel formulas - worksheet "input_combined" tbl_input_combined product build products amount total serviced products distinct serial number rate of serviced products A 12 5 42% B 8 4 50% C 10 6 60% This results in this pivot table / chart (that's basically what I'd like to achieve without using a helper column and regular forumas):
Solved
M4x
2 years ago Place DAX Commands and Tips
1.3KViews
0likes
5Comments
Dynamic calculation of YTD and ETC based on the selected month
The article is about dynamic calculation of YTD and ETC based on the selected month.
v-alq-msft
2 years ago Place Power BI Community Blog
7.9KViews
1like
1Comment
Dynamic Cumulative Totals on Pivoted Table
I have a table with pivoted data as shown below. I want to create a display a cumulative staff totals by year, for either of the Attributes selected on a Slicer object (education level or gender). How do I design a solution (a cumulative total) that re-calculates cumulative totals based on user selections in a Slicer? So far, I've used a standard cumulative total calculation, but it does not provide accurate results: CumulativeStaffTotal = CALCULATE( SUM(Merge1[netchange]), FILTER(ALLSELECTED(Merge1), Merge1[Value]=EARLIER(Merge1[Value]) && Merge1[year]=EARLIER(Merge1[year]) )) What approach would you use to solve this problem?
Solved
bcohen
3 years ago Place DAX Commands and Tips
597Views
0likes
1Comment
Dax Calculated Table
I am trying to take the sample dataset: ID Location Number Bracket 123 North America $101-$250 233 North America $251-$500 433 EMEA $101-$250 697 APAC $501-$1000 And create a calculated table in DAX that performs the following: Index Bracket North America APAC EMEA 1 $101-$250 1 0 1 2 $251-$500 1 0 0 3 $501-$1000 0 1 0 Essentially creating a pivot table. Let me know if this is possible. Thank you!
Solved
botaac
3 years ago Place DAX Commands and Tips
926Views
0likes
1Comment
Make column numbering not consecutive over the whole dataset but just for equal column names
Make column numbering not consecutive over the whole dataset but just for equal column names
v-jingzhang
4 years ago Place Power BI Community Blog
2.4KViews
3likes
0Comments
Return last non-blank string value of ticket (probably) with LASTNONBLANK
I am struggling with getting only the last non blank value for each ticket. In the Power Pivot measure, I have used a function LASTNONBLANK() , but the outcome was not reliable - probably because data model does not consider the original order of rows. Excel formula or VBA is not an option and Power Query is not very efficient because of big load of data. ticket step timestamp value 1 1 7/28/2021 04:15 PM 1 2 7/28/2021 04:23 PM a 1 3 7/28/2021 04:30 PM 1 4 7/28/2021 04:37 PM b 1 5 7/28/2021 04:44 PM 1 6 7/28/2021 04:51 PM 1 7 7/28/2021 04:59 PM 2 1 7/29/2021 01:51 PM a 2 2 7/29/2021 02:49 PM b 2 3 7/29/2021 03:47 PM c 2 4 7/29/2021 04:44 PM 2 5 7/29/2021 05:42 PM a 2 6 7/29/2021 06:39 PM For ticket "1", the returned value should be "b" (step 4) and for ticket "2", the returned value should be "a" (step 5). ticket value 1 b 2 a
Solved
MalyMajo
5 years ago Place DAX Commands and Tips
913Views
0likes
2Comments
Fully functional dynamic Pivot table visual
A fully functional pivot table visual where the report developer can configure the allowed fields in the pivot visual would be a great addition. Visual should allows users to rearrange rows and columns, add filters, add/remove fields from the list of fields and measures provided etc. This would allow users to view the data different ways and manipulate the views on the fly within a fairly controlled environment of the report itself.
vickyd
5 years ago Place Custom Visuals Ideas (read-only)
8.6KViews
7likes
3Comments
Require Color Coding For Pivot Table in Matrix Visual (Power BI Server - May 2020)
Hi, Can someone please help me to color code the entire row based on the text value for "Bench category" even if the value is empty or null. Right now in BI, I have obtained below output: But the final output should look like this as shown below: Thank you in advance for the help.
Anonymous
5 years ago Place Report Server
1.1KViews
0likes
2Comments