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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Mitchell92
Helper II
Helper II

Adding Switch Results to Matrix (or different solution)

Hi all,

 

I'm hoping someone may be able to assist.

 

I have a list of invoice #'s with invoice date and amount, and also a calendar table with a relationship between the invoice date and calendar date.

 

On the report, I want to be able to select the calendar date using a slicer, and have a table populate the age of the invoice (per selected date), and summarise the invoice amounts by an "age bracket" within a matrix.

 

I have achieved two things thus far:

1) I have been able to calculate the invoice age per selected slicer date using the following measure:

 
Age2 =
VAR _RangeStart = MIN(AR[TRXDTE])
VAR _RangeEnd = MAX(DateTable[Date])
RETURN
DATEDIFF(_RangeStart,_RangeEnd,DAY)
 

2) I have created a measure with a switch formula to distribute ages between age brackets:

 
AgeBrackets = switch( true,
[Age2] <= 30, "0 - 30", [Age2] <= 60, "31 - 60", [Age2] <= 90, "61 - 90", [Age2] < 120, "91 - 120","120+")
 
What I am unable to achieve is putting the AgeBrackets measure into a matrix as the column headers (with invoice value as values) to summarise this effectively.
 
I'm hoping someone has experience with this / may be able to assist? Hoping its a quick and easy solution.
 
PBIX LINK:
 
Thanks in advance.

 

 

 

5 REPLIES 5
DataInsights
Super User
Super User

@Mitchell92,

 

Create a disconnected table like the one below (no relationships). Sort Age Bracket by Age Bracket Index.

 

DataInsights_0-1716383071568.png

 

Create measures:

 

Sum of Total = SUM ( ARITEMA2[Total] )
AgeBrackets = 
VAR vLowerBound =
    SELECTEDVALUE ( AgeBracket[Lower Bound] )
VAR vUpperBound =
    SELECTEDVALUE ( AgeBracket[Upper Bound] )
VAR vAge = [Age2]
VAR vResult =
    IF ( vAge >= vLowerBound && vAge <= vUpperBound, [Sum of Total] )
RETURN
    vResult

 

Add AgeBracket[Age Bracket] to matrix columns, and [AgeBrackets] to matrix values. You can expand this example to handle totals with a measure like this:

 

AgeBrackets = 
VAR vLowerBound =
    SELECTEDVALUE ( AgeBracket[Lower Bound] )
VAR vUpperBound =
    SELECTEDVALUE ( AgeBracket[Upper Bound] )
VAR vAge = [Age2]
VAR vResult =
    SUMX ( VALUES ( HEADER[OPS] ), IF ( vAge >= vLowerBound && vAge <= vUpperBound, [Sum of Total] ) )
RETURN
    vResult

 





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

Proud to be a Super User!




Hi @DataInsights , thank you for your assistance. This has certainly got me a lot further - I have one issue though. On my actual data set, theres about 4 years worth of invoicing. This solution seems to be lumping everything into the max bracket within the category (everything is being categorised as 120+).

 

Wondering if there is a quick solution for that?

 

Thanks in advance.

@Mitchell92,

 

Would you be able to provide an example of data that appears in the wrong category? You could add additional rows to the shared pbix if that's easier.





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

Proud to be a Super User!




Hi @DataInsights ,

 

PBIX link here: TEST - Debtors Point in Time2.pbix

 

Hopefully you can assist.

 

Thanks,

 

Mitch

@Mitchell92,

 

Try this measure:

 

AgeBrackets = 
VAR vLowerBound =
    SELECTEDVALUE ( AgeBracket[Lower Bound] )
VAR vUpperBound =
    SELECTEDVALUE ( AgeBracket[Upper Bound] )
VAR vMaxDate =
    MAX ( DateTable[Date] )
VAR vResult =
    SUMX (
        Invoicing,
        VAR vInvDate = Invoicing[Date]
        VAR vAge = DATEDIFF ( vInvDate, vMaxDate, DAY )
        RETURN
            IF ( vAge >= vLowerBound && vAge <= vUpperBound, [Sum of Total] )
    )
RETURN
    vResult

 

DataInsights_0-1718753138443.png

 





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

Proud to be a Super User!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

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

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors