Dividing within the same column in a table

Question

Hello,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;I have a table which contains values relating to different attributes and different time periods, for example (made up - mine is much larger with many more attributes!):&nbsp;CodeFinancial QuarterAttributeValue1.1_11Total cups20001.1_22Total cups22001.1_33Total cups25001.1_44Total cups26001.2_11Total plates101.2_22Total plates51.2_33Total plates121.2_44Total plates152.1_11Drinks had1002.1_22Drinks had2002.1_33Drinks had1502.1_44Drinks had205&nbsp;How can I create a measure which will calculate the number of Drinks Had as a proportion of the Total Cups, and still be able to filter by Financial Quarter? I've tried numerous different filter and calculate functions but cannot get it to work.&nbsp;Help would be much appreciated.&nbsp;Thank you.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

wiffle · Answer

If anyone could help it really would be appreciated, I’ve spent about 8 hours on this issue!

anonymous · Answer

Hey! Does this work?&nbsp;Total Cups by quarter =&nbsp;CALCULATE(SUM(Sheet1[Value]),FILTER('Sheet1',Sheet1[Financial Quarter] = EARLIER(Sheet1[Financial Quarter])) ,&nbsp;&nbsp;&nbsp; FILTER('Sheet1','Sheet1'[Attribute]="Total cups" ))&nbsp;You will have to filter by attribute to get rid of the "garbage"&nbsp;Also, if you have more quarters, you will just need to alter the quarters to 2017q1, 2018,q2 ie something that never repeats. Hope this helps!&nbsp;

ashish_mathur · Answer

Hi,&nbsp;Write this measure&nbsp;=CALCULATE(SUM(Data[Value]),Data[Attribute]="Drinks Had")/CALCULATE(SUM(Data[Value]),Data[Attribute]="Total Cups")&nbsp;Hope this helps.

v-cherch-msft · Answer

Hi&nbsp;Wiffle&nbsp;You may also create a measure as below. Measure =
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER ( Table1, Table1[Attribute] = "Drinks had" )
)
    / CALCULATE (
        SUM ( Table1[Value] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Financial Quarter] ),
            Table1[Attribute] = "Total cups"
        )
    )&nbsp;&nbsp;&nbsp;Regards,Cherie

wiffle · Answer

Hi Cherie,&nbsp;Thanks for this. I've tried it and the %'s don't come out as I would expect them to (its saying 5% when it should be 8% based on manual calculation). I wonder if it's because I didn't fully explain all the other dimensions in my data set. There are other columns, such as 'area' - so you will have quarter, attribute and value for each area. Could it be that the ALLEXCEPT function is removing some of these? I don't really understand how the ALLEXCEPT function works sorry.&nbsp;Thank you

Forum Discussion

Dividing within the same column in a table

6 Replies

Related content

Divide columns

Divide two columns

Divide two columns DAX

Divide columns

column should be divided in to two columns based on another column

Code	Financial Quarter	Attribute	Value
1.1_1	1	Total cups	2000
1.1_2	2	Total cups	2200
1.1_3	3	Total cups	2500
1.1_4	4	Total cups	2600
1.2_1	1	Total plates	10
1.2_2	2	Total plates	5
1.2_3	3	Total plates	12
1.2_4	4	Total plates	15
2.1_1	1	Drinks had	100
2.1_2	2	Drinks had	200
2.1_3	3	Drinks had	150
2.1_4	4	Drinks had	205