Aggregation Problem in a Line Graph

[ghutchins]

I need a line graph that visualizes the Average % by day based on rows of data. Location may report multiple times a day or skip days.

• Counted is a total that met a condition out of a Population
• DAX Calculated % is a measure that divides Counted by Population

DAX Calculated % = 1 -
DIVIDE(
SUM(Counted),
SUM(Population)
)

• The line graph is set up as Axis = Date and Values = DAX Calculated %
• Expect to see one data point...  Average %...  per day in the line graph

What is needed based on the tables below is a line graph with 2 data points, 4/1/2020 at 75.4% and 4/10/2020 at 83.4%. The problem that I cannot get around is the line graph is instead further aggregated by site. Based on the data below my line graph shows 5 data points for 4/1/2020 and 5 data points for 4/10/2020... one data point for each site for each day.

I fail to write the correct DAX to arrive at Average %.. Thanks for any help in advance.

Sample data from Data Source

Location	Date	Counted	Population	DAX Calculated %
A	4/1/2020	9	10	90%
B	4/1/2020	8.8	10	88%
C	4/1/2020	9.9	10	99%
D	4/1/2020	10	10	100%
A	4/1/2020	0	10	0%
B	4/10/2020	4.5	10	45%
B	4/10/2020	9.4	10	94%
C	4/10/2020	9.5	10	95%
D	4/10/2020	10	01	100%

Expected data the line graph would use to plot line, (Average % is not a field value, Measure, or Column).

Date	Average %
4/1/2020	75.4%
4/10/2020	83.4%

[Anonymous]

Hi @ghutchins 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem or share me with your pbix file from your onedrive business.

Best Regards,

Rico Zhou

[ghutchins]

@Anonymousno it did not.  To new to Power BI to understand why it worked for the randomized data example but not in my actual data set. Maybe due to the % being a calculated column and not just a list of values.

[Anonymous]

Hi @ghutchins 

If you want to calculate the average by DAX Calculated % measure, you may try my way.

I use measure to achieve your goal.

Avg =

var _a = SUMX(ALLEXCEPT('Table','Table'[Date]),[DAX Calculated %])

var _b = CALCULATE(COUNT('Table'[Location]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])))

return

DIVIDE(_a,_b)

Result:

You can download the pbix file from this link: Aggregation Problem in a Line Graph

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[andre]

basically, you gotta average it twice, first time by site and then by day... 

you need to do a variation of this calc but in your case you need to do values(site[siteid]) instead of values(customer[customerid])

Average Days Between Orders = 
AVERAGEX(
    ADDCOLUMNS(
        VALUES(Customer[CustomerID]),
        "Average Days Between Orders by Customer", 
        AVERAGE(Sales[Days Since Last Order])
    ),
    [Average Days Between Orders by Customer]
)

i explain how this calc works at 4:15 of this video

if you want to practice, you can find the model here: https://businessintelligist.com/2020/07/26/dax-tutorial-average-days-between-orders/

[ghutchins]

@andre

after listening to your video and applying it to my data I find that I have a flat 95% across the date range and still aggregated by Location.  Had to duplicate DAX Calculated % Measure as a Column to get your calculation accept it.   No joy.

Average All Locations by Date=

AVERAGEX(

    ADDCOLUMNS(

        VALUES(Location),

        "Average by Date",

        AVERAGE(DAX Calculated %)

    ),

    [Average by Date]

)

[parry2k]

@ghutchins create another measure for average

Avg % = 
AVERAGEX ( VALUES ( Table[Date] ), [Your % Measure] )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[ghutchins]

@parry2kYour AVERAGEX calculation did not work...  I still have multiple Locations for a single Date