cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TonyBI Frequent Visitor
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

ITEMVALUEDATE
A1201/01/2019
B301/01/2019
C201/01/2019
D201/01/2019
E401/01/2019
F301/01/2019
G401/01/2019
H701/01/2019
I901/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

ITEMVALUEDATE
A1201/01/2019
A1102/01/2019
A1103/01/2019
B301/01/2019
B302/01/2019
C206/01/2019
C407/01/2019
C208/02/2019
C209/02/2019
D201/01/2019
D102/01/2019
F413/01/2019
F318/01/2019
G405/01/2019
T406/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:

 

ITEMVALUEDATE
A1201/01/2019
B301/01/2019
C201/01/2019
D201/01/2019
F301/01/2019
G401/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
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])

1.png

 

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.

View solution in original post

4 REPLIES 4
Community Support Team
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.

1.png

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

2.png

 

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

TonyBI Frequent Visitor
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). 

 

ITEMVALUEDATE
A1201/01/2019
A1101/02/2019
A1101/03/2019
B401/01/2019
B301/02/2019
C201/01/2019
C401/03/2019
C201/08/2019
C209/02/2019
D201/01/2019
D101/11/2019
F412/01/2019
F301/14/2019
G405/01/2019
T406/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:

 

ITEMVALUEDATE
A1101/03/2019
B301/02/2019
C201/08/2019
D101/11/2019
F301/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.

 

Thx in advance!

 

Community Support Team
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])

1.png

 

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.

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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?

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

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!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors