Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JarnoVisser
Helper I
Helper I

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

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

View solution in original post

5 REPLIES 5
LivioLanzo
Solution Sage
Solution Sage

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!  

v-yulgu-msft
Employee
Employee

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.

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

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.