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):
It's not two tables, it's four. Here's the data model
Then you can create your measure as needed.
I used an implicit DISTINCTCOUNT for the columns, in Excel you will need to create an explicit measure.
See attached.
Thank you very much, that's what I've needed 😀
I replaced Products from tbl_serviced_products with Products from tbl_Products and now it's working 🙄 😁
Hint for myself or everybody else who's trying to achieve the same things: To be able to filter by build year, I've to add another column "build year" to tbl_serviced_products and link Year in tbl_year with build year in tbl_serviced_products. In my previous example files, I had Year in tbl_calendar linked to service year in tbl_serviced_products.
Thanks a lot, now I'm trying to adapt this to my original dataset.
It's not two tables, it's four. Here's the data model
Then you can create your measure as needed.
I used an implicit DISTINCTCOUNT for the columns, in Excel you will need to create an explicit measure.
See attached.
Thank you very much for your reply!
Unfortunately, using Power BI is not possible for me (yet). I've recreated the data model you've shown in Excel and created three measures according to your example:
- rate of service - part 1: Distinct count of SN (working ✔️)
=DISTINCTCOUNT(tbl_serviced_products[serial number])
- rate of service - part 2: SUM of build products (not working ❌ - I'm getting the total amount but I need it according to the product within the same row. This isn't a surprise, please have a look at my "statement" at the bottom)
=SUM(tbl_build_products[amount of products build])
- rate of service - part 3: Divide 1 by 2 (working ✔️ - but the result isn't what I'm looking for of course)
=DIVIDE([rate of service - part 1 (Distinctcount SN)];[rate of service - part 2 (SUM build products)])
I've the feeling that you're aware of that and you've already tried to tell me that but I didn't get it... 🙄
Here's the example file: example_combine_two_tables_in_pivot_chart_4
Helpful resources
New forum boards available in Real-Time Intelligence.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
