cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Row By Row Division

Hi All, I have some data set like Categories and type wise and analysis the gap%.  Thanks in advance.

Input:

Category   Type            Week 1    Week 2    Week 3

K                 Sub                6                6             6

L                 Sub                 5                5             5

Output:

Category   Type            Week 1    Week 2    Week 3

K                 Sub                6                6             6

K                 Gap               60%          60%        60%

L                 Sub                 5                5             5

L                  Gap               50%           50%        50%

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Row By Row Division

Open Edit queris,

unpivot columns "week1"~"week3"

Rename column name "Attribute" to "week"

Close&&apply

Create two measures

```gap =
CALCULATE (
SUM ( Table1[Value] ),
FILTER (
ALLEXCEPT ( Table1, Table1[Category], Table1[week] ),
Table1[Type ] = "Sub"
)
)
/ CALCULATE (
SUM ( Table1[Value] ),
FILTER (
ALLEXCEPT ( Table1, Table1[Category], Table1[week] ),
)
)

Measure_value = IF(HASONEVALUE(Table1[Type ]),SUM(Table1[Value]),FORMAT([gap],"0%"))```

Add [Measure_value] in a matrix as below

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
Frequent Visitor

## Re: Row By Row Division

The solutions for your mayor request is posible doing a previous table tranformation using pivot/unpivot to types as Colums and weeks as rows
Community Support Team

## Re: Row By Row Division

Open Edit queris,

unpivot columns "week1"~"week3"

Rename column name "Attribute" to "week"

Close&&apply

Create two measures

```gap =
CALCULATE (
SUM ( Table1[Value] ),
FILTER (
ALLEXCEPT ( Table1, Table1[Category], Table1[week] ),
Table1[Type ] = "Sub"
)
)
/ CALCULATE (
SUM ( Table1[Value] ),
FILTER (
ALLEXCEPT ( Table1, Table1[Category], Table1[week] ),
)
)

Measure_value = IF(HASONEVALUE(Table1[Type ]),SUM(Table1[Value]),FORMAT([gap],"0%"))```

Add [Measure_value] in a matrix as below

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.