Crostable : sum(value) for col2=col1 where col1 is a dimension ?

[BTRD]

Hi everybody, Let's say I have a table with 2 columns col1 and col2, and a value column :

col1 col2 value date

A C 1 03/09/2024

C A 1 07/09/2024

G C 1 01/09/2024

K C 2 25/09/2024

P A 1 14/09/2024

Now, I want a crosstable with col1 as Y dimension, and month as X dimension where I can have the sum(value) for col2=col1 on agregate level :

col1 July 2024 

A 2

C 4

G 0

K 0

P 0

I have tried to use calculate and filter, but never get the right result ?

Any idea ?

[Anonymous]

Hi @BTRD ,
Your problem is to use the values in col1 to see how many times they appear in col2 and add up their values, right?

We can use the SUMX function to help you accomplish your needs!

Measure = 
SUMX(ALL('Table'),
IF(
    'Table'[Col2]=MAX('Table'[Col1]),
    'Table'[Value],
    0))

If you need to separate each month for calculations, we're going to create a calculated column, find their MONTH, and then use it for the conditional judgment in sumx.

MonthYear = FORMAT('Table'[date],"MMM YYYY")

Measure = 
SUMX(ALL('Table'),
   IF('Table'[Col2]=MAX('Table'[Col1])&&'Table'[MonthYear]=MAX('Table'[MonthYear]),
   'Table'[Value],
   0))
    

I hope my thoughts can solve your problem, if you have more recent asynchronous problems, you can contact me at any time, I will reply to you as soon as I see your message!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[BTRD]

Thanks a lot, but I still got some very strange results.

I'm a PowerBi beginner, I'm gonna try to make my data samples easier to check the formula.

[Anonymous]

Hi @BTRD ,
Hope all is well with you.
On my end, the current requirements are complete and working fine. However, you seem to be experiencing some issues on your end. In order to resolve this issue, could you please provide some data to help troubleshoot? Specifically, would it be possible to upload the example data and PBIX files you are using? This way I can better analyze the problem and give a solution.
Also, please let me know the current status of your progress and if there is anything I can do to assist on my end.
Thank you very much for your cooperation and I look forward to hearing from you!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[BTRD]

Hi, thanks for your help. It seems to work a bit, but I have the same amount for each month, it doesn't seems to take into account the month dimension of the crosstable for exemple.

[Selva-Salimi]

@BTRD 

Ok, then you should update the measure as follows:

Measure =

var selected_col1 = SELECTEDVALUE(My_table[Col1])

return

calculate(sum(My_table[Value]) , filter (all(My_table) , My_table[Col2]= selected_col1 && My_table[yearmonth] = selectedvalue (My_table[yearmonth]))+0

* create column yearmonth in your table based on FORMAT(date,YYYYMM)

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.

[Selva-Salimi]

@BTRD 

Did you use a matrix? col1 as rows, date as columns, and measure as values ?? please share images

[Selva-Salimi]

Hi @BTRD 

you can write a measure as follows: 

Measure =

var selected_col1 = SELECTEDVALUE(My_table[Col1])

return

calculate(sum(My_table[Value]) , filter (all(My_table) , My_table[Col2]= selected_col1))+0

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.