cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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
Solution Sage

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

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!

Microsoft Employee

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.
Helper I

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

Solution Sage

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
)
)```
Helper I

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors
Top Kudoed Authors