Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Wiffle
Regular Visitor

Dividing within the same column in a table

Hello,                    

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!):

 

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

 

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.

 

Help would be much appreciated.

 

Thank you.                       

6 REPLIES 6
v-cherch-msft
Employee
Employee

Hi @Wiffle

 

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"
        )
    ) 

 

 

1.png

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Cherie,

 

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.

 

Thank you

Hi @Wiffle

 

If it is not your case, please share more about data sample and expected output. 

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ashish_Mathur
Super User
Super User

Hi,

 

Write this measure

 

=CALCULATE(SUM(Data[Value]),Data[Attribute]="Drinks Had")/CALCULATE(SUM(Data[Value]),Data[Attribute]="Total Cups")

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hey! Does this work?

 

Total Cups by quarter =
 CALCULATE(SUM(Sheet1[Value]),FILTER('Sheet1',Sheet1[Financial Quarter] = EARLIER(Sheet1[Financial Quarter])) ,    FILTER('Sheet1','Sheet1'[Attribute]="Total cups" ))

 

You will have to filter by attribute to get rid of the "garbage"

 

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!

 

83.PNG

Wiffle
Regular Visitor

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.