Forum Discussion

Robert1981's avatar
Robert1981
Icon for Helper III rankHelper III
3 years ago

Subtotal / total under switch categories

Hi All,

 

I am using the SWITCH function. I created a separate table and I am able to display the categories on the Y-Axis while I show time on the X-axis.
However the row totals don't seem to work, when using the SWITCH. The total row shows up, but the values are not displaying. I checked with different font and background changes, but it is not a esthetic issue. It is simply not calculating.
I know I can add the "Total" as a category to my SWITCH, but I can't seem to figure out how to change font and background color on 1 specific category in the switch.
Who can help me resolve this....

 

Need Help
Tips and Tricks

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Robert1981 , You might need

     

    Sumx(Values(Table[Value1]) , <Switch Code>)

     

    If this does not help
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • Robert1981's avatar
    Robert1981
    Icon for Helper III rankHelper III
    3 years ago

    Thank you.
    It didn't quite work for me though. It is hard to create a sample data as the report is linked.

    Here is what I did:
    The table is a huge table with all postings to the Accounts Payable Subledger (both new invoices and received payments). Based on the date an item was cleared, and the end of month calculation, it calculates the open balance.
    The for the table in the first post, I break down the open balance by how many days items are open.
    I use the following formulas:

    Basic Open Balance:

    Open AP =
    VAR CurrentDate = MAX('DATE'[Date])
    VAR Open_Invoice =
    Calculate(
        Sum('Subledger AP'[Amount_USD]),
        ALL('DATE'),
        'Subledger AP'[Posting Date.1]<=CurrentDate,
        ISBLANK('Subledger AP'[Actuals Clearing Date(Date Format)])
        ||'Subledger AP'[Actuals Clearing Date(Date Format)]>CurrentDate
    )
    RETURN
    Open_Invoice*-1


    The I created a separate table with the breakdown of the days, to be used in the switch:
    Aging_Analysis =
    var selectedComp = SELECTEDVALUE(FA_Breakdown[Value1])
    var CurrentDate=Max('DATE'[Date])

    return
        SWITCH(selectedComp,
            "I. 0-30 Days",
                Calculate([Open AP],
                Datediff('Subledger AP'[Net Due Date(Date Format)],Currentdate,day)<=0 &&
                Datediff('Subledger AP'[Net Due Date(Date Format)],Currentdate,day)>-31),
            "II. 31-60 Days",
                Calculate([Open AP],
                Datediff('Subledger AP'[Net Due Date(Date Format)],Currentdate,day)<=-31 &&
                Datediff('Subledger AP'[Net Due Date(Date Format)],Currentdate,day)>-61),
            "III. 61-90 Days",
                Calculate([Open AP],
                Datediff('Subledger AP'[Net Due Date(Date Format)],Currentdate,day)<=-61 &&
                Datediff('Subledger AP'[Net Due Date(Date Format)],Currentdate,day)>-91),
            "IV. >90 Days",
                Calculate([Open AP],
                Datediff('Subledger AP'[Net Due Date(Date Format)],Currentdate,day)<=-91)
        )