cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Divide filtered values (by other table) by total values

Estimates, thanks in advance and sorry for my english. Here is the thing, I have a table with values my month and item, as shown (this table contain every month, but the estructure is the same - item, value, month - I only show january here):

TABLE 1

 ITEM VALUE DATE A 12 01/01/2019 B 3 01/01/2019 C 2 01/01/2019 D 2 01/01/2019 E 4 01/01/2019 F 3 01/01/2019 G 4 01/01/2019 H 7 01/01/2019 I 9 01/01/2019 …

The above table contains every value for every item (unique values) for every month. Now, I have another table that contains a number of the above items, and in some cases they are repeated (the values here are kind of certified values of the table 1)

TABLE 2

 ITEM VALUE DATE A 12 01/01/2019 A 11 02/01/2019 A 11 03/01/2019 B 3 01/01/2019 B 3 02/01/2019 C 2 06/01/2019 C 4 07/01/2019 C 2 08/02/2019 C 2 09/02/2019 D 2 01/01/2019 D 1 02/01/2019 F 4 13/01/2019 F 3 18/01/2019 G 4 05/01/2019 T 4 06/02/2019

I need to do two things...

1. I need to be able to sum the values of the first table but only for those items in the sencond table in the same month... this would be the next table:

 ITEM VALUE DATE A 12 01/01/2019 B 3 01/01/2019 C 2 01/01/2019 D 2 01/01/2019 F 3 01/01/2019 G 4 01/01/2019 SUM >> 26

2. the other thing that would be nice to do is to sum the latest value found on TABLE 2 (for every month) and divide it by the total sum of TABLE 1 (for the same month). This way I would be able to know, for every month, the percentage of certified values from the total.

Thanks!!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Divide filtered values (by other table) by total values

Hi @TonyBI

Based on my previous solution, i add the following steps to acheive your second goal.

1.create a column in Table1

`month_total = CALCULATE(SUM(Table1[VALUE]),ALLEXCEPT(Table1,Table1[YEAR/MONTH1]))`

2. create measures in Table2

```Measure 2 = CALCULATE(MAX(Table2[DATE]),ALLEXCEPT(Table2,Table2[ITEM],Table2[YEAR/MONTH2]))

Measure 3 = CALCULATE(SUM(Table2[VALUE]),FILTER(Table2,Table2[DATE]=[Measure 2]))

Measure 4 = SUMX(ALLEXCEPT(Table2,Table2[YEAR/MONTH2]),[Measure 3])

Measure 5 = CALCULATE(MAX(Table1[month_total]),FILTER(Table2,Table2[YEAR/MONTH2]=MAX(Table1[YEAR/MONTH1])))

Measure 6 = IF([Measure 5]<>BLANK(),[Measure 4]/[Measure 5])```

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 REPLIES 4
Community Support Team

## Re: Divide filtered values (by other table) by total values

Hi @TonyBI

As there is no value for Item C and G at 201901 in your example data, so the output of the sum total for 201901 is 20 in my test.

Look at my test:

1. create two columns in tablea and table2

columns in table 1

```YEAR/MONTH1 = FORMAT(Table1[DATE],"YYYYMM")

Column+merge1 = Table1[ITEM]&FORMAT(Table1[DATE],"yyyymm")```

columns in table2

```YEAR/MONTH2 = FORMAT(Table2[DATE],"YYYYMM")

Column+merge2 = Table2[ITEM]&FORMAT(Table2[DATE],"yyyymm")```

2. create relationship as below

3.create a measure in table2

```Measure =
VAR SUM1 =
CALCULATE (
SUM ( Table1[VALUE] ),
ALLEXCEPT ( Table2, Table2[ITEM], Table2[YEAR/MONTH2] )
)
VAR SUM2 =
SUMX (
ALL ( Table1 ),
CALCULATE (
SUM ( Table1[VALUE] ),
ALLEXCEPT ( Table1, Table1[ITEM], Table1[YEAR/MONTH1] )
)
)
RETURN
IF (
ISINSCOPE ( Table2[ITEM] ),
SUM1,
IF ( ISINSCOPE ( Table2[YEAR/MONTH2] ), SUM2, 0 )
)
```

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team

## Re: Divide filtered values (by other table) by total values

Hi @TonyBI

I don't undertsand this " the other thing that would be nice to do is to sum the latest value found on TABLE 2 (for every month)".

Please give me an example based on the example data.

Let to say, for item A in 201901?

Best Regards
Maggie

Frequent Visitor

## Re: Divide filtered values (by other table) by total values

Maggie! thx very much!

To clarify the second part of the question, this is what I mean:

Table 2 has every value for each month (I changed the values to be more specific - Notice that some items have many values for the same month, but diffent days).

 ITEM VALUE DATE A 12 01/01/2019 A 11 01/02/2019 A 11 01/03/2019 B 4 01/01/2019 B 3 01/02/2019 C 2 01/01/2019 C 4 01/03/2019 C 2 01/08/2019 C 2 09/02/2019 D 2 01/01/2019 D 1 01/11/2019 F 4 12/01/2019 F 3 01/14/2019 G 4 05/01/2019 T 4 06/02/2019

So, I would like to sum Every last value for each item, each month. In this case (january only), this should be the result:

 ITEM VALUE DATE A 11 01/03/2019 B 3 01/02/2019 C 2 01/08/2019 D 1 01/11/2019 F 3 01/14/2019

Total sum should be 20. Finally, I would divide "20" by total values of table 1 ("46" for january).

This will allow me to know the percentage of certified values by month.

Community Support Team

## Re: Divide filtered values (by other table) by total values

Hi @TonyBI

Based on my previous solution, i add the following steps to acheive your second goal.

1.create a column in Table1

`month_total = CALCULATE(SUM(Table1[VALUE]),ALLEXCEPT(Table1,Table1[YEAR/MONTH1]))`

2. create measures in Table2

```Measure 2 = CALCULATE(MAX(Table2[DATE]),ALLEXCEPT(Table2,Table2[ITEM],Table2[YEAR/MONTH2]))

Measure 3 = CALCULATE(SUM(Table2[VALUE]),FILTER(Table2,Table2[DATE]=[Measure 2]))

Measure 4 = SUMX(ALLEXCEPT(Table2,Table2[YEAR/MONTH2]),[Measure 3])

Measure 5 = CALCULATE(MAX(Table1[month_total]),FILTER(Table2,Table2[YEAR/MONTH2]=MAX(Table1[YEAR/MONTH1])))

Measure 6 = IF([Measure 5]<>BLANK(),[Measure 4]/[Measure 5])```

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!