Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have 5 columns:
Ranks, City, Date, Category, Value
CAP AKL 12/4 AVAILABLE 24
CAP AKL 12/4 OFF 4
CAP AKL 13/4 AVAILABLE 25
CAP AKL 13/4 OFF 4
CAP WLG 12/4 AVAILABLE 19
CAP WLG 13/4 OFF 3
FO AKL 12/4 AVAILABLE 22
FO AKL 13/4 OFF 5
FO WLG 12/4 AVAILABLE 18
FO CHC 12/4 AVAILABLE 13
I am trying to get the total number of "Available" Category 'values'. However, to do this I want to have the 'average' of every 'available' value in each city for the period (which is about 4months) and then SUM up the average of each City for each Rank.
How do i do this?
Thanks
Solved! Go to Solution.
Hi @michaelsparrow,
To get the sum of average, you can directly get the desired result in source table via creating calculated columns. Please refer to below DAX formulas.
Average of available = CALCULATE ( AVERAGE ( 'SUM of AVERAGE'[Value] ), FILTER ( ALLEXCEPT ( 'SUM of AVERAGE', 'SUM of AVERAGE'[City] ), 'SUM of AVERAGE'[Category] = "AVAILABLE" ) ) Average of OFF = CALCULATE ( AVERAGE ( 'SUM of AVERAGE'[Value] ), FILTER ( ALLEXCEPT ( 'SUM of AVERAGE', 'SUM of AVERAGE'[City] ), 'SUM of AVERAGE'[Category] = "OFF" ) ) OFF/Available = 'SUM of AVERAGE'[Average of OFF] / 'SUM of AVERAGE'[Average of available] Sum = CALCULATE ( SUM ( 'SUM of AVERAGE'[OFF/Available] ), ALLEXCEPT ( 'SUM of AVERAGE', 'SUM of AVERAGE'[Ranks] ) )
Further more, you can summarize above table to get below output.
New Table = SUMMARIZE ( 'SUM of AVERAGE', 'SUM of AVERAGE'[Ranks], 'SUM of AVERAGE'[City], "Sum", MAX ( 'SUM of AVERAGE'[Sum] ) )
Best regards,
Yuliana Gu
Just to help clarify, do you have an example of what you expect the result to be?
Might this work??
New Table = SUMMARIZE( 'Table1', [City], "Average of Available",CALCULATE(AVERAGE('Table1'[Value]),'Table1'[Category]="AVAILABLE") )
Great this has worked very well. I am nearly there.
1 more question. Using the method you provided, I have made 2 tables to get 2 Grouped values. The 2 tables are related as attached. I have provided snips of the 2 tables data.
What I am looking to do now is to do a SUM(Average of OFF/Average of Available). The problem is that they aren't linked directly. How do i do this?
Cheers
Hi @michaelsparrow,
To get the sum of average, you can directly get the desired result in source table via creating calculated columns. Please refer to below DAX formulas.
Average of available = CALCULATE ( AVERAGE ( 'SUM of AVERAGE'[Value] ), FILTER ( ALLEXCEPT ( 'SUM of AVERAGE', 'SUM of AVERAGE'[City] ), 'SUM of AVERAGE'[Category] = "AVAILABLE" ) ) Average of OFF = CALCULATE ( AVERAGE ( 'SUM of AVERAGE'[Value] ), FILTER ( ALLEXCEPT ( 'SUM of AVERAGE', 'SUM of AVERAGE'[City] ), 'SUM of AVERAGE'[Category] = "OFF" ) ) OFF/Available = 'SUM of AVERAGE'[Average of OFF] / 'SUM of AVERAGE'[Average of available] Sum = CALCULATE ( SUM ( 'SUM of AVERAGE'[OFF/Available] ), ALLEXCEPT ( 'SUM of AVERAGE', 'SUM of AVERAGE'[Ranks] ) )
Further more, you can summarize above table to get below output.
New Table = SUMMARIZE ( 'SUM of AVERAGE', 'SUM of AVERAGE'[Ranks], 'SUM of AVERAGE'[City], "Sum", MAX ( 'SUM of AVERAGE'[Sum] ) )
Best regards,
Yuliana Gu
i am also having the similar kind of requirement , can you please have a look on below link.
https://community.powerbi.com/t5/Desktop/Sum-of-Average/m-p/288865#M127101
thanks,
I have 5 columns:
Ranks, City, Date, Category, Value
CAP AKL 12/4 AVAILABLE 24
CAP AKL 12/4 OFF 4
CAP AKL 13/4 AVAILABLE 25
CAP AKL 13/4 OFF 4
CAP WLG 12/4 AVAILABLE 19
CAP WLG 13/4 OFF 3
FO AKL 12/4 AVAILABLE 22
FO AKL 13/4 OFF 5
FO WLG 12/4 AVAILABLE 18
FO CHC 12/4 AVAILABLE 13
I am trying to get the total number of "Available" Category 'values'. However, to do this I want to have the 'average' of every 'available' value in each city for the period (which is about 4months) and then SUM up the average of each City for each Rank.
How do i do this?
Thanks
I believe that your situation is analogous to my Super Groups post:
https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |