Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Difference Column in Matrix for Multiple Values displayed as Rows

Hi All, 

I have a following Matrix where Rows are Values:

 

Is there any way to create a Measure or something, that would add another column to this matrix with a % difference for all of the Value rows for Pre / Post (between two displayed Totals)?

Sorry if this was already posted somewhere.

Thanks in advance!

Need Help
Tips and Tricks

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous 

    It may be impossible to get the difference column based on your current data model.

    I make some transformation in Edit queries,

    Click from "impression" to "margin" columns (columns added in the "Value" field of your matrix),select "unpovit columns"

    original table

    period adtype impression click conversion cost conversionvalue margin
    pre other 64991 1597 58.01 1114.1 1735.81 621.71
    pre paused 146536 2807 139.32 2925.75 3879.34 953.59
    post other 169774 4790 257.16 4003.32 7075.83 3072.51

    after transforming

    Apply&&close,

    create measures

    Measure = CALCULATE(SUM(Table1[Value]),ALLEXCEPT(Table1,Table1[Attribute],Table1[period]))

Measure 2 = 
CALCULATE([Measure],FILTER(Table1,[period]="pre"))/CALCULATE([Measure],FILTER(Table1,[period]="post"))

Measure 3 = IF(HASONEVALUE(Table1[period]),[Measure],[Measure 2])

    If adding measure2 in the matrix, it shows strangely.

    as a workaround, add measure2 in another table like my last table visual.

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Maggie,

       

      Thansks for your responce!

      Thats a nice workarownd, I give you credit for that!

      Howewer, it doesnt fully satisfy my needs.

      For example, I was going to create some addititonal measures for metrics like CTR (Clicks / Impressions). For this Measure, I wont be able to calculate change Pre vs Post, as it wont be a part of Atribute - Value columns.

       

      Do you maybe have same ideas for that as well?

      Regards,

      Oleksii 

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        7 years ago

        Hi Anonymous 

        How does the measure calculate?

        Does the measure use the column in the same table as "period","adtype"...?

        Could you show an exmaple for me to test?

         

        Best Regards

        Maggie