Forum Discussion

parvathisuku_90's avatar
parvathisuku_90
Helper I
6 years ago
Solved

Need help with multi line chart

_keygrid_element_keydateqty_alarms_7dayduration_alarms_7day
f25fc369-2e73-49d1-a6fe-16cb06b4b1060fdd05a9-6977-4dac-8266-ffc42de6f1712010-01-0145.389173.814148
f25fc369-2e73-49d1-a6fe-16cb06b4b1060fdd05a9-6977-4dac-8266-ffc42de6f1712010-01-0278.2493374.81478
f25fc369-2e73-49d1-a6fe-16cb06b4b1060fdd05a9-6977-4dac-8266-ffc42de6f1712010-01-035.85745873.57437
f25fc369-2e73-49d1-a6fe-16cb06b4b1060fdd05a9-6977-4dac-8266-ffc42de6f1712010-01-042.51378813.53965
f25fc369-2e73-49d1-a6fe-16cb06b4b1060fdd05a9-6977-4dac-8266-ffc42de6f1712010-01-0592.1478110.05942
f25fc369-2e73-49d1-a6fe-16cb06b4b1060fdd05a9-6977-4dac-8266-ffc42de6f1712010-01-0655.608886.40046
f25fc369-2e73-49d1-a6fe-16cb06b4b1060fdd05a9-6977-4dac-8266-ffc42de6f1712010-01-0730.9946127.20225
f25fc369-2e73-49d1-a6fe-16cb06b4b1060fdd05a9-6977-4dac-8266-ffc42de6f1712010-01-0823.8049333.04217
f25fc369-2e73-49d1-a6fe-16cb06b4b1060fdd05a9-6977-4dac-8266-ffc42de6f1712010-01-0997.9097354.01713
f25fc369-2e73-49d1-a6fe-16cb06b4b1060fdd05a9-6977-4dac-8266-ffc42de6f1712010-01-1052.9711832.85847
f25fc369-2e73-49d1-a6fe-16cb06b4b1060fdd05a9-6977-4dac-8266-ffc42de6f1712010-01-114.6389836.54067
f25fc369-2e73-49d1-a6fe-16cb06b4b1060fdd05a9-6977-4dac-8266-ffc42de6f1712010-01-1262.975128.00264
f25fc369-2e73-49d1-a6fe-16cb06b4b1060fdd05a9-6977-4dac-8266-ffc42de6f1712010-01-1310.7997341.60459
a7c9be1c-a379-4907-b7b3-bd9b88a8128f13b3fe33-537c-4f93-babc-72529b6fafbb2019-02-0154.2826265.38411
a7c9be1c-a379-4907-b7b3-bd9b88a8128f13b3fe33-537c-4f93-babc-72529b6fafbb2019-02-0279.6589433.39506
a7c9be1c-a379-4907-b7b3-bd9b88a8128f13b3fe33-537c-4f93-babc-72529b6fafbb2019-02-0363.242423.931321
a7c9be1c-a379-4907-b7b3-bd9b88a8128f13b3fe33-537c-4f93-babc-72529b6fafbb2019-02-0420.5851623.90161
a7c9be1c-a379-4907-b7b3-bd9b88a8128f13b3fe33-537c-4f93-babc-72529b6fafbb2019-02-0547.2075436.48876
a7c9be1c-a379-4907-b7b3-bd9b88a8128f13b3fe33-537c-4f93-babc-72529b6fafbb2019-02-0655.907690.79858
a7c9be1c-a379-4907-b7b3-bd9b88a8128f13b3fe33-537c-4f93-babc-72529b6fafbb2019-02-074.15258424.72576
a7c9be1c-a379-4907-b7b3-bd9b88a8128f13b3fe33-537c-4f93-babc-72529b6fafbb2019-02-0833.7211478.22688
a7c9be1c-a379-4907-b7b3-bd9b88a8128f13b3fe33-537c-4f93-babc-72529b6fafbb2019-02-0915.9277778.50816
a7c9be1c-a379-4907-b7b3-bd9b88a8128f13b3fe33-537c-4f93-babc-72529b6fafbb2019-02-1092.20765.64891
a7c9be1c-a379-4907-b7b3-bd9b88a8128f13b3fe33-537c-4f93-babc-72529b6fafbb2019-02-1194.7717422.67628
a7c9be1c-a379-4907-b7b3-bd9b88a8128f13b3fe33-537c-4f93-babc-72529b6fafbb2019-02-1218.6825690.84214
a7c9be1c-a379-4907-b7b3-bd9b88a8128f13b3fe33-537c-4f93-babc-72529b6fafbb2019-02-1343.141663.74307
893566d0-d49d-40d2-9865-ab164880ee580321e4e3-3b80-4cf5-8b24-4cb583094fe02020-03-0128.9595375.98046
893566d0-d49d-40d2-9865-ab164880ee580321e4e3-3b80-4cf5-8b24-4cb583094fe02020-03-0255.7543652.99937
893566d0-d49d-40d2-9865-ab164880ee580321e4e3-3b80-4cf5-8b24-4cb583094fe02020-03-0357.175724.701374
893566d0-d49d-40d2-9865-ab164880ee580321e4e3-3b80-4cf5-8b24-4cb583094fe02020-03-0420.751314.95151
893566d0-d49d-40d2-9865-ab164880ee580321e4e3-3b80-4cf5-8b24-4cb583094fe02020-03-0591.8285139.45962
893566d0-d49d-40d2-9865-ab164880ee580321e4e3-3b80-4cf5-8b24-4cb583094fe02020-03-0694.6423292.85916
893566d0-d49d-40d2-9865-ab164880ee580321e4e3-3b80-4cf5-8b24-4cb583094fe02020-03-0775.8281335.08725
893566d0-d49d-40d2-9865-ab164880ee580321e4e3-3b80-4cf5-8b24-4cb583094fe02020-03-0866.7492921.11391
893566d0-d49d-40d2-9865-ab164880ee580321e4e3-3b80-4cf5-8b24-4cb583094fe02020-03-091.23189649.76245
893566d0-d49d-40d2-9865-ab164880ee580321e4e3-3b80-4cf5-8b24-4cb583094fe02020-03-1011.7725346.74265
893566d0-d49d-40d2-9865-ab164880ee580321e4e3-3b80-4cf5-8b24-4cb583094fe02020-03-1141.538350.992634
     

 

- data from all grid_element_id in gray(except 0321e4e3-3b80-4cf5-8b24-4cb583094fe0)
– data from a single grid_element_id highlighted in blue( lets take 0321e4e3-3b80-4cf5-8b24-4cb583094fe0 as exmple)
– data averaged across all grid_element_id is shown in black

– the points on the x axis come from the "date" column
– the points on the y axis come from the "qty_alarms_7day" column

 

Can some one please help me

  • Anonymous's avatar
    Anonymous
    6 years ago

    HI parvathisuku_90,

    So you want to create a line chart with a graph of all categories of raw table records except selected items and display two lines of aggregate record line and selected item line for comparison?

    If this is a case, I'd like to suggest you create a new table stored value of category 'selected', "others" to use on the line chart as legends.

    After these steps, you can use the raw table id field to create a slicer and write measure formulas to interact with slicer to get results based on different line groups.

    Measure =
VAR currLegend =
    SELECTEDVALUE ( Category[Legend] )
VAR selected =
    ALLSELECTED ( RawTable[Key] )
RETURN
    SWITCH (
        currLegend,
        "Selected",
            CALCULATE (
                AVERAGE ( RawTable[Amount] ),
                FILTER ( ALLSELECTED ( RawTable ), [Key] IN selected ),
                VALUES ( RawTable[Date] )
            ),
        "Others",
            CALCULATE (
                AVERAGE ( RawTable[Amount] ),
                FILTER ( ALLSELECTED ( RawTable ), NOT ( [Key] IN selected ) ),
                VALUES ( RawTable[Date] )
            )
    )

    Regards,

    Xiaoxin Sheng

4 Replies

    • parvathisuku_90's avatar
      parvathisuku_90
      Helper I
      6 years ago

      These two scenarios are acheived in the above chart

      – data from a single grid_element_id highlighted in blue( lets take 0321e4e3-3b80-4cf5-8b24-4cb583094fe0 as exmple)
      – data averaged across all grid_element_id is shown in black

       

      But not able to acheive this

      - data from all grid_element_id in gray(except 0321e4e3-3b80-4cf5-8b24-4cb583094fe0)

       

      Consider each distinct grid_element_id as one asset, then 3 assets are available in the data . In that one is showing in blue line. Other all(dynamically changes, in this case two) should show in light grey. How can I add those to the exsisting chart( with same line colour).

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        parvathisuku_90 , you can average measure that will respond to filter

        and a measure like this

         

        new measure =
        calculate(Avergae(Table[duration_alarms_7day]), filter(All(Table), not(Table[grid_element_key] in values(Table[grid_element_key]))))

        new measure =
        calculate(Avergae(Table[duration_alarms_7day]), filter(All(Table), not(Table[grid_element_key] in allselected(Table[grid_element_key]))))

         

        You can use allselected(Table) in filter in place of all(Table)

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI parvathisuku_90,

    So you want to create a line chart with a graph of all categories of raw table records except selected items and display two lines of aggregate record line and selected item line for comparison?

    If this is a case, I'd like to suggest you create a new table stored value of category 'selected', "others" to use on the line chart as legends.

    After these steps, you can use the raw table id field to create a slicer and write measure formulas to interact with slicer to get results based on different line groups.

    Measure =
VAR currLegend =
    SELECTEDVALUE ( Category[Legend] )
VAR selected =
    ALLSELECTED ( RawTable[Key] )
RETURN
    SWITCH (
        currLegend,
        "Selected",
            CALCULATE (
                AVERAGE ( RawTable[Amount] ),
                FILTER ( ALLSELECTED ( RawTable ), [Key] IN selected ),
                VALUES ( RawTable[Date] )
            ),
        "Others",
            CALCULATE (
                AVERAGE ( RawTable[Amount] ),
                FILTER ( ALLSELECTED ( RawTable ), NOT ( [Key] IN selected ) ),
                VALUES ( RawTable[Date] )
            )
    )

    Regards,

    Xiaoxin Sheng