cancel
Showing results for
Did you mean:
Frequent Visitor

Summarise table with condition

Hello

I have a transaction table with the following data:

 Transaction date Amount Urgency Customer 1-1-2017 50 high a 30-5-2017 100 medium a 30-9-2017 30 low a 1-1-2018 45 medium a 20-2-2018 55 low a

I want to summarise the data per year per urgency. Besides that each customer should be allocated to the transaction with the highest urgency within a year.

So finally I need to have:

 2017 2018 High 180 0 Medium 0 100 Low 0 0

Regards,

Jarno

1 ACCEPTED SOLUTION

Accepted Solutions
Established Member

Re: Summarise table with condition

If you follow the Star Schema approach which LivioLanzo showed, then you can also use the below Calculated column HighestUrgencyID in Data table, and create a relationship between Urgency[UrgencyID] & Data[HighestUrgencyID]. This way your measure will be just SUM(Data[Amount]).

```HighestUrgencyID =
VAR TransactionYear =
RELATED ( 'Calendar'[Year] )
VAR CurrentCustomer = 'Data'[Customer]
RETURN
CALCULATE (
MAX ( 'Data'[UrgencyID] ),
FILTER (
'Data',
'Data'[Customer] = CurrentCustomer
&& RELATED ( 'Calendar'[Year] ) = TransactionYear
)
)```
5 REPLIES 5
Community Support Team

Re: Summarise table with condition

Hi @JarnoVisser,

```Rank =
IF ( Table2[Urgency] = "High", 1, IF ( Table2[Urgency] = "medium", 2, 3 ) )

Sum amount =
IF (
Table2[Rank]
= CALCULATE (
MIN ( Table2[Rank] ),
ALLEXCEPT ( Table2, Table2[Transaction date].[Year] )
),
CALCULATE (
SUM ( Table2[Amount] ),
ALLEXCEPT ( Table2, Table2[Transaction date].[Year] )
),
0
)
```

Use a Matrix to display data.

Best regards,

Yuliana Gu

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

Re: Summarise table with condition

Hi Yuliana,

It works fine for only one customer. But in my real data I have multiple customers like:

 Transaction date Amount Urgency Customer 1-1-2017 50 high a 30-5-2017 100 medium a 30-9-2017 30 low a 1-1-2018 45 medium a 20-2-2018 55 low a 1-1-2017 50 low b 30-5-2017 100 low b 30-9-2017 30 low b 1-1-2018 45 medium b 20-2-2018 55 high b

And with multiple customers it gives no amount for par example customer b in 2017. Do you have a solution for that?

Kind regards,

Jarno

Super User

Re: Summarise table with condition

try like this:

```Measure =
IF (
HASONEVALUE ( Urgencies[Urgency] ),
SUMX (
VALUES ( 'Calendar'[Year] ),
SUMX (
VALUES ( Customers[Customer] ),
IF (
CALCULATE (
MAX ( Data[Urgency ID] ),
ALL ( Urgencies )
) = SELECTEDVALUE ( Urgencies[Urgency ID] ),
CALCULATE (
SUM ( Data[Amount] ),
ALL ( Urgencies )
),
0
)
)
)
)```

Proud to be a Datanaut!

Established Member

Re: Summarise table with condition

If you follow the Star Schema approach which LivioLanzo showed, then you can also use the below Calculated column HighestUrgencyID in Data table, and create a relationship between Urgency[UrgencyID] & Data[HighestUrgencyID]. This way your measure will be just SUM(Data[Amount]).

```HighestUrgencyID =
VAR TransactionYear =
RELATED ( 'Calendar'[Year] )
VAR CurrentCustomer = 'Data'[Customer]
RETURN
CALCULATE (
MAX ( 'Data'[UrgencyID] ),
FILTER (
'Data',
'Data'[Customer] = CurrentCustomer
&& RELATED ( 'Calendar'[Year] ) = TransactionYear
)
)```
Frequent Visitor

Re: Summarise table with condition

Thank you all! The most easy one even to verify is the solution of Akhil. The syntax of his solution should only be written as follows:

```HighestUrgencyID =
VAR TransactionYear = RELATED ( 'Calendar'[Year] )
VAR CurrentCustomer = RELATED ('Dim'[Customer]
RETURN
CALCULATE (
MIN ( 'Data'[UrgencyID] ),
FILTER (
'Data',
'Data'[Customer] = CurrentCustomer
&& 'Data'[Year] ) = TransactionYear
)
)```