cancel
Showing results for
Did you mean:
Highlighted
mohitchugh 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
danielpenafiel 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.