Forum Discussion

titus-sensei's avatar
titus-sensei
New Member
3 years ago

Waterfall chart filtered to only one breakdown put wrong category value

Hello,

i made a waterfall with a trick to get a starting value and the delta value on each breakdown.
it's working well with all or some breakdowns selected, but if i select only one, the starting value doesn't work as expected and return only the delta (and so the end value is wrong as well with 0)

I'll put a sample file and the code below, if anyone could help me.
Regards

Sample File 



Code of the mesure :

amount_wf =
VAR selectedBreakdown = SELECTEDVALUE(organisation[org_N2])
VAR selectedCategory = SELECTEDVALUE(category[category])
RETURN
 /* [REAL - BUDGET] =
    CALCULATE(
        SUM(fact_table[amount])
        ,FILTER(category, category[category] = "REAL")
    )
    - CALCULATE(
        SUM(fact_table[amount])
        ,FILTER(category, category[category] = "BUDGET")
    )*/
SWITCH(selectedCategory
    ,"BUDGET"
    ,SWITCH(selectedBreakdown
        ,"aa"
        ,-1 * [REAL - BUDGET]
        ,"ab"
        ,-1 * [REAL - BUDGET]
        ,"ac"
        ,-1 * [REAL - BUDGET]
        , // else -> category BUDGET
        CALCULATE(
            SUM(fact_table[amount])
            ,FILTER(ALL(category), category[category] = "BUDGET")
        )
    )
    ,"REAL"
    ,SWITCH(selectedBreakdown
        ,"aa"
        ,0
        ,"ab"
        ,0
        ,"ac"
        ,0
        , // else -> category REAL
        CALCULATE(
            SUM(fact_table[amount])
            ,FILTER(ALL(category), category[category] = "REAL")
        )
    )
)
Need Help

2 Replies

  • titus-sensei's avatar
    titus-sensei
    New Member
    3 years ago

    I found out that the " , // else -> category BUDGET" and the " , // else -> category REAL" parts are completly ignored when there is only one breakdown value.
    I tried putting an arbitrary "10" value in it and the result was the same on the third graph, the delta in the starting category and 0 in the end (first 2 graph had 10 in start and end).

    is this because of the switch ? is there any other way to populate the values ?

    Regards