cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mohitchugh Frequent Visitor
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                 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%

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Row By Row Division

Hi @mohitchugh 

Open Edit queris,

unpivot columns "week1"~"week3"

Rename column name "Attribute" to "week"

2.png3.png

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

4.png 

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
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
Community Support Team

Re: Row By Row Division

Hi @mohitchugh 

Open Edit queris,

unpivot columns "week1"~"week3"

Rename column name "Attribute" to "week"

2.png3.png

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

4.png 

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.