Row By Row Division

Question

Hi All, I have some data set like Categories and type wise and analysis the gap%.&nbsp; Thanks in advance.&nbsp;Input:Category&nbsp; &nbsp;Type&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Week 1&nbsp; &nbsp; Week 2&nbsp; &nbsp; Week 3K&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Add&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10K&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Sub&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6L&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Add&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10L&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Sub&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp;Output:Category&nbsp; &nbsp;Type&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Week 1&nbsp; &nbsp; Week 2&nbsp; &nbsp; Week 3K&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Add&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10K&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Sub&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6K&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Gap&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;60%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 60%&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;60%&nbsp;L&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Add&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10L&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Sub&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5L&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Gap&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50%&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;50%

v-juanli-msft · Accepted Answer

Hi&nbsp;mohitchugh&nbsp;
Open Edit queris,
unpivot columns "week1"~"week3"
Rename column name "Attribute" to "week"

Close&amp;&amp;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&nbsp;]),SUM(Table1[Value]),FORMAT([gap],"0%"))
Add [Measure_value] in a matrix as below
&nbsp;
Best RegardsMaggie
&nbsp;
Community Support Team _ Maggie LiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Row By Row Division

2 Replies

Related content

remove duplicate value within a row

DAX Calculation (Row by Row Division)

Matrix Row Conditional Formatting

Issue with Formulas - When Multiple Rows Similar Rows Exist

Filter rows