topic Re: Combining two tables in a pivot chart / calculating SUM table 2 based on row label of table 1 in DAX Commands and Tips

Combining two tables in a pivot chart / calculating SUM table 2 based on row label of table 1

M4x — Fri, 03 Nov 2023 15:45:22 GMT

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):

Re: Combining two tables in a pivot chart / calculating SUM table 2 based on row label of table 1

lbendlin — Sat, 04 Nov 2023 22:59:51 GMT

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.

Re: Combining two tables in a pivot chart / calculating SUM table 2 based on row label of table 1

M4x — Sun, 05 Nov 2023 23:44:21 GMT

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

Re: Combining two tables in a pivot chart / calculating SUM table 2 based on row label of table 1

lbendlin — Sun, 05 Nov 2023 23:58:00 GMT

Is the value in column A coming from the tbl_Products table?

Re: Combining two tables in a pivot chart / calculating SUM table 2 based on row label of table 1

M4x — Mon, 06 Nov 2023 08:51:01 GMT

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.

Re: Combining two tables in a pivot chart / calculating SUM table 2 based on row label of table 1

M4x — Mon, 13 Nov 2023 19:47:34 GMT

I've adapted this to my original dataset and it's working great, thanks again lbendlin ! 😁