Row By Row Division

[mohitchugh]

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                 Add              10             10           10

K                 Sub                6                6             6

L                 Add               10              10           10

L                 Sub                 5                5             5

Output:

Category   Type            Week 1    Week 2    Week 3

K                 Add              10             10           10

K                 Sub                6                6             6

K                 Gap               60%          60%        60%

L                 Add               10              10           10

L                 Sub                 5                5             5

L                  Gap               50%           50%        50%

[v-juanli-msft · Accepted solution]

Hi @mohitchugh 

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] ),
            Table1[Type ] = "Add"
        )
    )


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.

[danielpenafiel]

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