Graphs on Matrix

[Anonymous]

I have order numbers, sub order numbers, planned time and actual time for each sub order. 1 order having multiple sub orders. I want to find count of orders where, summation of actual time is greater than summation of planned time. There are thousands of entries for orders.

In other words, I want pivot table of my data and then using that result i want to plot graphs. I want to do it using Dax.

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can use "SUM" function in Dax to calculate summation of planned time/ actual time. I create a sample to have a test.

Measure:

Count of Orders Sum of Actual time > Sum of Planned time = 
VAR _T =
    SUMMARIZE (
        'Table',
        'Table'[Order numbers],
        "Planned time Duration", CALCULATE ( SUM ( 'Table'[Planned time] ) ),
        "Actual time Duration", CALCULATE ( SUM ( 'Table'[Actual time] ) )
    )
RETURN
    COUNTAX (
        FILTER ( _T, [Actual time Duration] > [Planned time Duration] ),
        [Order numbers]
    )

In my sample, only "Order 2" 's summation of actual time is greater than summation of planned time. So measure should return 1. Result is as below. 

Best Regards,
Rico Zhou

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

[amitchandak]

@Anonymous , Not very clear. In the visual like bar, you can explore small multiple 

or I matrix you try add trend line (sparklines) etc -https://community.powerbi.com/t5/Quick-Measures-Gallery/SVG-Sparklines-Line/td-p/486271