cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JarnoVisser Frequent Visitor
Frequent Visitor

Summarise table with condition

Hello

 

I have a transaction table with the following data:

 

Transaction dateAmountUrgencyCustomer
1-1-201750higha
30-5-2017100mediuma
30-9-201730lowa
1-1-201845mediuma
20-2-201855lowa

 

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:

 

 20172018
High1800
Medium0100
Low00

 

I would appreciate your help!

Regards,

Jarno

1 ACCEPTED SOLUTION

Accepted Solutions
AkhilAshok Established Member
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
Community Support Team

Re: Summarise table with condition

Hi @JarnoVisser,

 

Add calculated columns:

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.

1.PNG2.PNG

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.
JarnoVisser Frequent Visitor
Frequent Visitor

Re: Summarise table with condition

Hi Yuliana,


Thank you for your reply!

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

Transaction dateAmountUrgencyCustomer
1-1-201750higha
30-5-2017100mediuma
30-9-201730lowa
1-1-201845mediuma
20-2-201855lowa
1-1-201750lowb
30-5-2017100lowb
30-9-201730lowb
1-1-201845mediumb
20-2-201855highb

 

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

Thanks in advance!

 

Kind regards,

Jarno

Super User
Super User

Re: Summarise table with condition

Hi @JarnoVisser

 

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

 

Capture3.PNG

 

 

Capture.PNGCapture1.PNGCapture2.PNG

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

AkhilAshok Established Member
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
        )
    )
JarnoVisser Frequent Visitor
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
        )
    )