Forum Discussion
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...
Hi Arghya_Dutta ,
Please provide more details, extract for where?
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
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
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.
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.