Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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" ) )
Regards,
Cherie
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
Hi,
Write this measure
=CALCULATE(SUM(Data[Value]),Data[Attribute]="Drinks Had")/CALCULATE(SUM(Data[Value]),Data[Attribute]="Total Cups")
Hope this helps.
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!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |