Forum Discussion

Arghya_Dutta's avatar
Arghya_Dutta
Frequent Visitor
1 year ago

make a table by using matrix

How to extract the data completely from the matrix visual in a tabular form. So that I can use that summarise data in any form for lookup and other things dynamically. The outputs are showing from m...
Tips and Tricks
  • Arghya_Dutta's avatar
    Arghya_Dutta
    Frequent Visitor
    1 year ago

    I have a matrix from my Part Wise CS table In which 

    Rows Contains

    1. Part Number 
    2. Part Name

    Columns Contains:

    Month

    Values Contains
    Qty
    STDV ---------------Its a measure
    Avg.-----------------Its a measure
    Check---------------Its a measure

    this is my check measure

    Check = value ( (CALCULATE(STDEV.P('Part Wise CS'[Qty]),'Part Wise CS'[Month]<>SWITCH(
        MONTH(MAX(Calender[Date])),
        1, "January",
        2, "February",
        3, "March",
        4, "April",
        5, "May",
        6, "June",
        7, "July",
        8, "August",
        9, "September",
        10, "October",
        11, "November",
        12, "December"
    )) + CALCULATE(AVERAGE('Part Wise CS'[Qty]),'Part Wise CS'[Month]<>SWITCH(
        MONTH(MAX(Calender[Date])),
        1, "January",
        2, "February",
        3, "March",
        4, "April",
        5, "May",
        6, "June",
        7, "July",
        8, "August",
        9, "September",
        10, "October",
        11, "November",
        12, "December"
    )))*0.3 +CALCULATE(STDEV.P('Part Wise CS'[Qty]),'Part Wise CS'[Month]<>SWITCH(
        MONTH(MAX(Calender[Date])),
        1, "January",
        2, "February",
        3, "March",
        4, "April",
        5, "May",
        6, "June",
        7, "July",
        8, "August",
        9, "September",
        10, "October",
        11, "November",
        12, "December"
    )) + CALCULATE(AVERAGE('Part Wise CS'[Qty]),'Part Wise CS'[Month]<>SWITCH(
        MONTH(MAX(Calender[Date])),
        1, "January",
        2, "February",
        3, "March",
        4, "April",
        5, "May",
        6, "June",
        7, "July",
        8, "August",
        9, "September",
        10, "October",
        11, "November",
        12, "December"
    ))-
    CALCULATE(SUM('Part Wise CS'[Qty]),
    'Part Wise CS'[Month]=
    SWITCH(
        MONTH(MAX(Calender[Date])),
        1, "January",
        2, "February",
        3, "March",
        4, "April",
        5, "May",
        6, "June",
        7, "July",
        8, "August",
        9, "September",
        10, "October",
        11, "November",
        12, "December"
    )))

    This is my STDV measure

    STDV =
    VAR LatestMonth =
        SWITCH(
            MONTH(MAX('Calender'[Date])),
            1, "January",
            2, "February",
            3, "March",
            4, "April",
            5, "May",
            6, "June",
            7, "July",
            8, "August",
            9, "September",
            10, "October",
            11, "November",
            12, "December"
        )
    VAR ValidRows =
        FILTER(
            'Part Wise CS',
            'Part Wise CS'[CSD_PNO] = MAX('Part Wise CS'[CSD_PNO])
        )
    VAR DataExcludingLatestMonth =
        FILTER(
            ValidRows,
            'Part Wise CS'[Month] <> LatestMonth
        )
    VAR Result =
        CALCULATE(
            STDEV.P('Part Wise CS'[Qty]),
            DataExcludingLatestMonth
        )
    RETURN
    IF(
        COUNTROWS(DataExcludingLatestMonth) > 1,
        Result,
        BLANK()
    )

    This is my Avg. measure

    Avg. = CALCULATE(AVERAGE('Part Wise CS'[Qty]),'Part Wise CS'[Month]<>SWITCH(
        MONTH(MAX(Calender[Date])),
        1, "January",
        2, "February",
        3, "March",
        4, "April",
        5, "May",
        6, "June",
        7, "July",
        8, "August",
        9, "September",
        10, "October",
        11, "November",
        12, "December"
    ))

    The measures changes there values as per my selected months from Slicer of Month. this month is from Calender Table. 

    The Part wise CS and Calender table is connected through a common column which is concatenate. 
    Now in matrix visual I am getting part number, part name, Avg., Stdv and Check

    I want to capture that Check values which is actually Rows total against each Part number which should be dynamically. So That I can use that check measures values against each part number as per selected slicer for look up with other table.