Visualize cycle times on clustered column chart where the cycle times are the small multiples?

[Anonymous]

Hi,

I have the following dataset, which says that to get from A to B, it takes 6 days, from B to C 17 days, etc., and the cycle goes on and on under different PIDS and dates + cycle times.

Now, the following result should be similar to this, and I'm saying similar, because this shows years instead of months, and the values are incorrect as well (I took a snapshot from a practice example, hence the values down below).

As you can see, I need to create measure and timespan selectors, along with cycle day calculations.

So I made a TimeSpan selection, and a MEasure selection table, 

  

And for cycle times, I tried this approach:

A to B = SWITCH(TRUE(),

    AND(SELECTEDVALUE('Measure selection'[Measure selection])="Avg",SELECTEDVALUE('TimeSpan selection'[TimeSpan selection])="Days"),CALCULATE(AVERAGE(F_Main[Cycle time (Days)]),F_Main[Attribute]="A"),

    AND(SELECTEDVALUE('Measure selection'[Measure selection])="Avg",SELECTEDVALUE('TimeSpan selection'[TimeSpan selection])="Weeks"),CALCULATE(AVERAGE(F_Main[Cycle time (Days)])/7,F_Main[Attribute]="A"),

    AND(SELECTEDVALUE('Measure selection'[Measure selection])="Avg",SELECTEDVALUE('TimeSpan selection'[TimeSpan selection])="Months"),CALCULATE(AVERAGE(F_Main[Cycle time (Days)])/30,F_Main[Attribute]="A"))

Now, what this does, is that if I select AVG and Days, then I get the Average for A to B cycle in Days, and then I'm stuck. How am I supposed to proceed to be able to visualize like the one in the example?

[v-jianboli-msft · Accepted solution]

Hi @Anonymous ,

Please try:

A to B =
VAR _a =
    SELECTEDVALUE ( 'Measure selection'[Value] )
VAR _b =
    SELECTEDVALUE ( 'TimeSpan selection'[Value] )
VAR _calculate =
    SWITCH (
        TRUE (),
        _a = "Avg",
            CALCULATE (
                AVERAGE ( F_Main[Cycle time(Days)] ),
                FILTER ( 'F_Main', [Attribute] = "B" )
            ),
        _a = "Min",
            CALCULATE (
                MIN ( 'F_Main'[Cycle time(Days)] ),
                FILTER ( 'F_Main', [Attribute] = "B" )
            ),
        _a = "Max",
            CALCULATE (
                MAX ( 'F_Main'[Cycle time(Days)] ),
                FILTER ( 'F_Main', [Attribute] = "B" )
            )
    )
VAR _divide =
    SWITCH ( TRUE (), _b = "Days", 1, _b = "Weeks", 7, _b = "Months", 30 )
RETURN
    _calculate / _divide

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.