Hello I have a transaction table with the following data: Transaction dateAmountUrgencyCustomer1-1-201750higha30-5-2017100mediuma30-9-201730lowa1-1-201845mediuma20-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: 20172018High1800Medium0100Low00 I would appreciate your help!Regards,Jarno

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

Summarise table with condition | Microsoft Fabric Community

5 Replies

LivioLanzo

Solution Sage

7 years ago

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

v-yulgu-msft
Microsoft 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
  Helper 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 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?
  Thanks in advance!
  
  Kind regards,
  Jarno
  - AkhilAshok
    Solution 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 ) )