Forum Discussion

Mitchell92's avatar
Mitchell92
Icon for Helper II rankHelper II
2 years ago

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 calenda...
Need Help
DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
2 years ago

Mitchell92,

 

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

 

 

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

 

Mitchell92's avatar
Mitchell92
Icon for Helper II rankHelper II
2 years ago

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.