DAX measure with filters and divisions

[analyticspbi87]

Hi there,

With the input below,

Category	Type	Year	Value
Z	Apple	2021	50.00
Z	Apple	2022	150.00
A	Apple	2021	2500.00
A	Apple	2022	3000.00
A	Apple	2023	4000.00
B	Apple	2021	4250.00
B	Apple	2022	5000.00
B	Apple	2023	5500.00
C	Apple	2021	5500.00
C	Apple	2022	6000.00
C	Apple	2023	6100.00
X	Apple	2021	5000.00
X	Apple	2022	6000.00
X	Apple	2023	8000.00
Z	Orange	2021	50.00
Z	Orange	2022	50.00
A	Apple	2021	2500.00
A	Apple	2022	3000.00
A	Apple	2023	3000.00
B	Apple	2021	1000.00
B	Apple	2022	1300.00
B	Apple	2023	1850.00
C	Apple	2021	12.50
C	Apple	2022	615.00
C	Apple	2023	1617.50
X	Apple	2021	788.13
X	Apple	2022	945.75
X	Apple	2023	103.38

I need help converting data using a measure to convert

	2021	2022	2023
Z	100	200
A	5000	6000	7000
B	5250	6300	7350
C	5512.5	6615	7717.5
X	5788.125	6945.75	8103.375

to 

	2021	2022	2023
Z	100	200
A	50	30	35
B	52.5	31.5	36.75
C	55.125	33.075	38.5875
X	5788.125	6945.75	8103.375

Is there a measure where I can write, that can help turn the table above to the table below? Rows A-C will need to be divided by row Z (100) for 2021, and for 2022 onwards, Rows A-C will need be divided by row Z (200)

= IF(

AND(OR('Table'[Category]="A",'Table'[Category]="B",'Table'[Category]="C"),'Table'[Year]="2021")

DIVIDE(

SUM('Table'[Value]),

(CALCULATE(SUM('Table'[Value]),'Table'[Year]="2021",'Table'[Category]="Z")),

IF(OR('Table'[Category]="A",'Table'[Category]="B",'Table'[Category]="C")),

DIVIDE(

SUM('Table'[Value]),

(CALCULATE(SUM('Table'[Value]),'Table'[Year]="2022",'Table'[Category]="Z")),

Table'[Value])

[Anonymous · Accepted solution]

Hi @analyticspbi87 ,

Based on my testing, please try the following methods:

1.Create the simple table.

2.Create the new measure to calculate per year.

total per year = CALCULATE(SUM('Table'[Value]), ALLEXCEPT('Table','Table'[Category],'Table'[Year]))

3.Create the new measure to convert table.

convert = 
VAR z_2021 = CALCULATE(SUM('Table'[Value]), FILTER('Table', 'Table'[Category] = "Z" && 'Table'[Year] = 2021))
VAR z_2022 = CALCULATE(SUM('Table'[Value]), FILTER('Table', 'Table'[Category] = "Z" && 'Table'[Year] = 2022))
VAR year_ = SELECTEDVALUE('Table'[Year])
VAR CATE = SELECTEDVALUE('Table'[Category])
RETURN
IF(CATE IN {"A", "B", "C"} && year_ = 2021, DIVIDE([total per year], 100), IF(CATE IN {"A", "B", "C"} && year_ in {2022, 2023}, DIVIDE([total per year], 200), [total per year])
)

4.Drag the measure into the matrix visual.

5.The result is shown below.

Best Regards,

Wisdom Wu

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