COMPARE YEAR VALUES AND SHOW THE DIFFERENCE IN ICONS

[srkase]

i have the data like this..

NAME	YEAR	SALES
A	20152016	100
B	20152016	105
A	20162017	110
B	20162017	102
A	20172018	102
B	20172018	110
A	20182019	125
B	20182019	120
A	20192020	100
B	20192020	100

Now the result in matrix is 

NAME         20152016  20162017 20172018   20182019 20192020

A	100	110	102	125	100
B	105	102	110	120	100

What i want is 

compare value between years and show the result in symbols or icons in a separate column

Kindly help in this regards and thanks in advance

[v-eachen-msft]

Hi @srkase ,

You need to create a calculated column:

Trend =
VAR os =
    CALCULATE (
        FIRSTNONBLANK ( test[SALES], 1 ),
        FILTER (
            test,
            test[YEAR]
                = EARLIER ( test[YEAR] ) - 10001
                && test[NAME] = EARLIER ( test[NAME] )
        )
    )
VAR if1 =
    IF ( NOT ( ISBLANK ( os ) ), test[SALES] - os )
VAR up =
    UNICHAR ( 129029 )
VAR down =
    UNICHAR ( 129031 )
VAR nochange =
    UNICHAR ( 9644 )
RETURN
    IF (
        if1 > 0,
        up,
        IF ( if1 = BLANK (), BLANK (), IF ( if1 = 0, nochange, down ) )
    )

Then you can use Matrix visual to get result. Please turn off the row/column subtotals.

If you want learn more about UNICHAR(), you can refer to this document.
https://www.vertex42.com/ExcelTips/unicode-symbols.html

Note: This link contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

Best Regards,

Eads

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

[srkase]

[v-eachen-msft]

Hi @srkase ,

Please check the data type of PROD_CABLE[YEAR] and PROD_CABLE[TOTAL_CR], they need to be whole number or decimal number. Or you can use VALUE() to format them.

Best Regards,

Eads

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

[srkase]

the results are not showing correclty..

[v-eachen-msft]

Hi @srkase ,

I saw a column "MAJOR GROUP" in your code. What does it mean? You can try to replace "MAJOR GROUP" with "CATEGORY".

Best Regards,

Eads

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

[srkase]

I TRIED WITH REPLACING WITH CATEGORY TOO,... BUT IT DOESNT WORKS

I AM NOW GIVING THE SAMPLE DATA STRUCTURE...  I tried with all possibilities.. am using desktop BI ..

YEAR	SHORTNAME	DEALER	TYPE	MAJOR GROUP	MINOR GROUP	SUB GROUP	PRODUCT	VARIANT	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC	JAN	FEB	MAR	TOTAL QTY	TOTALVAL	PTA_DESTINATION	UNIT_CODE	PTA_DISTRICT	PTA_TALUK	PTA_PIN	ACP_ENTRYDT	HP	PER	APR VAL	MAY VAL	JUN VAL	JUL VAL	AUG VAL	SEP VAL	OCT VAL	NOV VAL	DEC VAL	JAN VAL	FEB VAL	MAR VAL	TOTAL CR	DLR NAME_DEST	DEST_DST_PIN	DST_DEST_PIN	PIN_DEST_DST	CATEGORY
20172018	TCR	A	STD	SUBMERSIBLES	6" SET	EMS+ESRS	EMS7MS + ESRS31-23	33AAA + 13FAA	1	0	0	0	0	0	0	0	0	0	0	0	1	15000		ASE1				########	7.5	0	0	0	0	0	0	0	0	0	0	0	0	0	0					PRODUCT
20172018	TCR	B	STD	SUBMERSIBLES	6" SET	EMS+ESRS	EMS5MS + ESRS31-15	33AAA + 13FAA	1	0	0	0	0	0	0	0	0	0	0	0	1	15000		ASE1				########	5	0	0	0	0	0	0	0	0	0	0	0	0	0	0					PRODUCT
20182019	TCR	A	STD	SUBMERSIBLES	6" SET	EMS+ESRS	EMS7MS + ESRS51-08	33AAA + 13FAA	1	0	0	0	0	0	0	0	0	0	0	0	1	15000		ASE1				########	7.5	0	0	0	0	0	0	0	0	0	0	0	0	0	0					PRODUCT
20192020	TCR	A	STD	SUBMERSIBLES	6" SET	EMS+ESRS	EMS5MS + ESRS42-10	33AAA + 13FAA	1	0	0	0	0	0	0	0	0	0	0	0	1	15000		ASE1				########	5	0	0	0	0	0	0	0	0	0	0	0	0	0	0					PRODUCT
20192020	TCR	A	STD	SUBMERSIBLES	6" SET	EMS+ESRS	EMS5MS + ESRS36-12	33AAA + 13FAA	1	0	0	0	0	0	0	0	0	0	0	0	1	15000		ASE1				########	5	0	0	0	0	0	0	0	0	0	0	0	0	0	0					PRODUCT
20182019	TCR	B	STD	SUBMERSIBLES	6" SET	ST+RS-S	ST075D + RS31SS/30	31AAA + 21FAA	1	0	0	0	0	0	0	0	0	0	0	0	1	15000		ASE1				########	10	0	0	0	0	0	0	0	0	0	0	0	0	0	0					PRODUCT
20172018	TCR	A	STD	SPARES	SPARES	SPARES	IMPELLER HCS725H / ASMSP725H (SS) (SP)	0	0	0	0	0	0	0	0	5	0	0	0	5	1900		ASE2				########		0	0	0	0	0	0	0	0	0	0	0	0	0	0					SPARES
20172018	TCR	A	STD	SPARES	SPARES	SPARES	IMP+DIFF ASSY JRF06 (SPS)	0	0	0	0	0	0	0	0	45	0	0	0	45	2000		ASE4				########		0	0	0	0	0	0	0	0	0	0	0	0	0	0					SPARES
20172018	TCR	B	STD	SPARES	SPARES	SPARES	IMP+DIFF ASSY JRF09 (SPS)	0	0	0	0	0	0	0	0	45	0	0	0	45	2100		ASE4				########		0	0	0	0	0	0	0	0	0	0	0	0	0	0					SPARES
20182019	TCR	B	STD	SPARES	SPARES	SPARES	DIFFUSER HOUSING RS30/35/40 (SPS)	0	0	0	0	0	0	0	0	12	0	0	0	12	2300		ASE1				########		0	0	0	0	0	0	0	0	0	0	0	0	0	0					SPARES
20182019	TCR	B	STD	SPARES	SPARES	SPARES	PAN HEAD SCREW MS M5 X 16 MM (SP)	0	0	0	0	0	0	0	0	100	0	0	0	100	2400		ASE2				########		0	0	0	0	0	0	0	0	0	0	0	0	0	0					SPARES

[v-eachen-msft]

Hi @srkase ,

I have already seen your data, but I still don't understand which column you want to put in “Rows”. At the same time, I found that Total CR has two or more values of a year, maybe you need to sum it by years.

Best Regards,

Eads

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