Forum Discussion

mohitchugh's avatar
mohitchugh
Frequent Visitor
7 years ago
Solved

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         ...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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's avatar
danielpenafiel
Frequent Visitor
7 years ago
The solutions for your mayor request is posible doing a previous table tranformation using pivot/unpivot to types as Colums and weeks as rows