Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi @all,
I have a requirement where any suggestions and logical way will be helpful.
I have a table like below with the data.
I want the matrix visual as below
The Column should have from Jan to Dec and CYTD, PYTD, Inc/Dec, Percent with all the Rows Qty1, Qty2, Qty3, Qty2/Qty1 and Qty3/Qty1. We will even have filters Region, Brand, Territory. The Matrix should update on selecting any of the filters.
The Values in the columns Jan to Dec will be latest year from table i.e., 2019 data. PYTD is Previous year YTD value.
Your suggestions will be helpfull in building in this Matrix. Thanks in Advance.
Solved! Go to Solution.
Hi @Anonymous ,
I did the following steps:
ID | Measure |
1 | Jan |
2 | Feb |
3 | Mar |
4 | Apr |
5 | May |
6 | Jun |
7 | Jul |
8 | Aug |
9 | Sep |
10 | Oct |
11 | Nov |
12 | Dec |
13 | CYTD |
14 | PYTD |
15 | INC/DEC |
16 | Percent |
Add the following measures:
Qty calculation = SUM(Data_Table[Value])
CYTD = TOTALYTD([Qty calculation];'Calendar'[Date])
PYTD = TOTALYTD([Qty calculation];DATEADD('Calendar'[Date];-1;YEAR))
Inc/Dec = [CYTD] - [PYTD]
Percent = [Inc/Dec]/[PYTD]
QTY 1 =
CALCULATE (
SWITCH (
TRUE ();
SELECTEDVALUE ( Measure_Order[ID] ) < 13; CALCULATE (
[Qty calculation];
FILTER (
ALL ( 'Calendar'[Month] );
'Calendar'[Month] = SELECTEDVALUE ( Measure_Order[ID] )
)
) + 0;
SELECTEDVALUE ( Measure_Order[ID] ) = 13; [CYTD] + 0;
SELECTEDVALUE ( Measure_Order[ID] ) = 14; [PYTD] + 0;
SELECTEDVALUE ( Measure_Order[ID] ) = 15; [Inc/Dec] + 0;
SELECTEDVALUE ( Measure_Order[ID] ) = 16; [Percent] * 100
);
Data_Table[Type] = "QTY1"
)
QTY 2 =
CALCULATE (
SWITCH (
TRUE ();
SELECTEDVALUE ( Measure_Order[ID] ) < 13; CALCULATE (
[Qty calculation];
FILTER (
ALL ( 'Calendar'[Month] );
'Calendar'[Month] = SELECTEDVALUE ( Measure_Order[ID] )
)
) + 0;
SELECTEDVALUE ( Measure_Order[ID] ) = 13; [CYTD] + 0;
SELECTEDVALUE ( Measure_Order[ID] ) = 14; [PYTD] + 0;
SELECTEDVALUE ( Measure_Order[ID] ) = 15; [Inc/Dec] + 0;
SELECTEDVALUE ( Measure_Order[ID] ) = 16; [Percent] * 100
);
Data_Table[Type] = "QTY2"
)
QTY 3 =
CALCULATE (
SWITCH (
TRUE ();
SELECTEDVALUE ( Measure_Order[ID] ) < 13; CALCULATE (
[Qty calculation];
FILTER (
ALL ( 'Calendar'[Month] );
'Calendar'[Month] = SELECTEDVALUE ( Measure_Order[ID] )
)
) + 0;
SELECTEDVALUE ( Measure_Order[ID] ) = 13; [CYTD] + 0;
SELECTEDVALUE ( Measure_Order[ID] ) = 14; [PYTD] + 0;
SELECTEDVALUE ( Measure_Order[ID] ) = 15; [Inc/Dec] + 0;
SELECTEDVALUE ( Measure_Order[ID] ) = 16; [Percent] * 100
);
Data_Table[Type] = "QTY3"
)
QTY2/QTY1 = DIVIDE([QTY 2] ;[QTY 1];0)
QTY3/QTY1 = DIVIDE([QTY 3] ;[QTY 1];0)
Then create your matrix with the following format:
If you want you can also make the Percent as a percentage however for that you need to create new measures to place in the matrix will be similar to this one:
QTY 1 (formatted) =
CALCULATE (
SWITCH (
TRUE ();
SELECTEDVALUE ( Measure_Order[ID] ) < 16; [QTY 1];
FORMAT ( [QTY 1] / 100; "###.00%" )
)
)
You need to have one for each of the QTY be aware that you cannot use these measures for the division within quantities because of the text format.
I added on the measure above so yo could see the result in the attach PBIX file.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous ,
First of all you need to create a calendar table that will allow to have the slicing of the dates and in this case the month columns on your matrix.
Now if you only were considering the Type as you have then you should place the values like this on the matrix:
Since you want to calculate additional values in this caseqt2/qt1 and qt3/qt1 you need to create a measure for each value of your Type column something similar to this:
QTY1 = CALCULATE(SUM(Table[Value]); Table[Type] = Qty1)
QTY2 = CALCULATE(SUM(Table[Value]); Table[Type] = Qty2)
QTY3 = CALCULATE(SUM(Table[Value]); Table[Type] = Qty3)
QTY2/QTY1 = [QTY2] / [QTY1]
QTY3/QTY1 = [QTY3] / [QTY1]
Now you should make the same steps as previously the only difference is that you should place the measures on the values and rows should not have any values.
For the other columns you need to have some different calculations added but if you can share a sample file would be easier to calculate it.
If you can share a sample in a format to be copy paste or a PBIX file would be helpfull.
Please see this post regarding How to Get Your Question Answered Quickly (courtesy of @Greg_Deckler) and How to provide sample data in the Power BI Forum (courtesy of @ImkeF).
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix ,
First of all Thanks for the reply.
I had created measures for Qty1, Qty2 , Qty3, Qty2/Qty1 and Qty3/Qty1. Also placed Types in Rows and Month in Column.
I am facing issue in adding Columns CYTD, PYTD, Inc/Dec and Percent next to Month in Column matrix.
Let me know how to fix that ?
Can you share some sample data in a format I can copy paste it to PBI?
Those measure need to have some additional calculations and some changes in the way they are calculated.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix ,
Here is the attached sample file. https://docs.google.com/spreadsheets/d/1BuilJnLr7Vf3GZxSFqmlmitpcMcx9J94YN20QQHQW_o/edit#gid=2867526...
Please let me know if you require anything else.
Hi @Anonymous ,
I did the following steps:
ID | Measure |
1 | Jan |
2 | Feb |
3 | Mar |
4 | Apr |
5 | May |
6 | Jun |
7 | Jul |
8 | Aug |
9 | Sep |
10 | Oct |
11 | Nov |
12 | Dec |
13 | CYTD |
14 | PYTD |
15 | INC/DEC |
16 | Percent |
Add the following measures:
Qty calculation = SUM(Data_Table[Value])
CYTD = TOTALYTD([Qty calculation];'Calendar'[Date])
PYTD = TOTALYTD([Qty calculation];DATEADD('Calendar'[Date];-1;YEAR))
Inc/Dec = [CYTD] - [PYTD]
Percent = [Inc/Dec]/[PYTD]
QTY 1 =
CALCULATE (
SWITCH (
TRUE ();
SELECTEDVALUE ( Measure_Order[ID] ) < 13; CALCULATE (
[Qty calculation];
FILTER (
ALL ( 'Calendar'[Month] );
'Calendar'[Month] = SELECTEDVALUE ( Measure_Order[ID] )
)
) + 0;
SELECTEDVALUE ( Measure_Order[ID] ) = 13; [CYTD] + 0;
SELECTEDVALUE ( Measure_Order[ID] ) = 14; [PYTD] + 0;
SELECTEDVALUE ( Measure_Order[ID] ) = 15; [Inc/Dec] + 0;
SELECTEDVALUE ( Measure_Order[ID] ) = 16; [Percent] * 100
);
Data_Table[Type] = "QTY1"
)
QTY 2 =
CALCULATE (
SWITCH (
TRUE ();
SELECTEDVALUE ( Measure_Order[ID] ) < 13; CALCULATE (
[Qty calculation];
FILTER (
ALL ( 'Calendar'[Month] );
'Calendar'[Month] = SELECTEDVALUE ( Measure_Order[ID] )
)
) + 0;
SELECTEDVALUE ( Measure_Order[ID] ) = 13; [CYTD] + 0;
SELECTEDVALUE ( Measure_Order[ID] ) = 14; [PYTD] + 0;
SELECTEDVALUE ( Measure_Order[ID] ) = 15; [Inc/Dec] + 0;
SELECTEDVALUE ( Measure_Order[ID] ) = 16; [Percent] * 100
);
Data_Table[Type] = "QTY2"
)
QTY 3 =
CALCULATE (
SWITCH (
TRUE ();
SELECTEDVALUE ( Measure_Order[ID] ) < 13; CALCULATE (
[Qty calculation];
FILTER (
ALL ( 'Calendar'[Month] );
'Calendar'[Month] = SELECTEDVALUE ( Measure_Order[ID] )
)
) + 0;
SELECTEDVALUE ( Measure_Order[ID] ) = 13; [CYTD] + 0;
SELECTEDVALUE ( Measure_Order[ID] ) = 14; [PYTD] + 0;
SELECTEDVALUE ( Measure_Order[ID] ) = 15; [Inc/Dec] + 0;
SELECTEDVALUE ( Measure_Order[ID] ) = 16; [Percent] * 100
);
Data_Table[Type] = "QTY3"
)
QTY2/QTY1 = DIVIDE([QTY 2] ;[QTY 1];0)
QTY3/QTY1 = DIVIDE([QTY 3] ;[QTY 1];0)
Then create your matrix with the following format:
If you want you can also make the Percent as a percentage however for that you need to create new measures to place in the matrix will be similar to this one:
QTY 1 (formatted) =
CALCULATE (
SWITCH (
TRUE ();
SELECTEDVALUE ( Measure_Order[ID] ) < 16; [QTY 1];
FORMAT ( [QTY 1] / 100; "###.00%" )
)
)
You need to have one for each of the QTY be aware that you cannot use these measures for the division within quantities because of the text format.
I added on the measure above so yo could see the result in the attach PBIX file.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCheck out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.