Forum Discussion

JarnoVisser's avatar
JarnoVisser
Icon for Helper I rankHelper I
7 years ago
Solved

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-...
Need Help
  • AkhilAshok's avatar
    AkhilAshok
    7 years ago

    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
        )
    )
v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

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.

Best regards,

Yuliana Gu

  • JarnoVisser's avatar
    JarnoVisser
    Icon for Helper I rankHelper I
    7 years ago

    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

    • AkhilAshok's avatar
      AkhilAshok
      Icon for Solution Sage rankSolution Sage
      7 years ago

      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's avatar
        JarnoVisser
        Icon for Helper I rankHelper I
        7 years ago

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