cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JesperTim Frequent Visitor
Frequent Visitor

Total average

Hi 

 

I am trying to create a total of averages in a Matrix and after that calculate average of the totals of these averages based on the filters in my BI.

 

Right now I have made this filter, but it only gives me the total of averages - need the averages of these numbers instead.

 

sum =
AVERAGEX(
    KEEPFILTERS(VALUES('Siemens Time data'[Product])),
    CALCULATE(AVERAGE('Siemens Time data'[CNC4]))+
AVERAGEX(
    KEEPFILTERS(VALUES('Siemens Time data'[Product])),
    CALCULATE(AVERAGE('Siemens Time data'[CNC6]))+
AVERAGEX(
    KEEPFILTERS(VALUES('Siemens Time data'[Product])),
    CALCULATE(AVERAGE('Siemens Time data'[CNC7]))))+
AVERAGEX(
    KEEPFILTERS(VALUES('Siemens Time data'[Product])),
    CALCULATE(AVERAGE('Siemens Time data'[CNC8]))))+
AVERAGEX(
    KEEPFILTERS(VALUES('Siemens Time data'[Product])),
    CALCULATE(AVERAGE('Siemens Time data'[CNC9]))+
AVERAGEX(
    KEEPFILTERS(VALUES('Siemens Time data'[Product])),
    CALCULATE(AVERAGE('Siemens Time data'[CNC10]))))
 
 
Any help is highly appreciated as I am stuck due to this problem. 
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Total average

Hi @JesperTim,

 

Sorry for my late respond. I can work part of this out. As I cannot understand the logic of the week11 and week12. Could you please share more information about that? Also please check the steps as below.

 

1. Create a calculated table and create relationship between it and the fact table.

 

Table = DISTINCT(UNION(VALUES('Data list'[Type2]),VALUES('Data list'[Type3])))

rela.PNG

 

2. Create a measure to get the target.

 

Target1 = 
VAR _tar =
    CALCULATE ( SUM ( 'Data list'[Target] ) )
RETURN
    IF (
        ISBLANK ( _tar ),
        CALCULATE (
            SUM ( 'Data list'[Target] ),
            USERELATIONSHIP ( 'Data list'[Type2], 'Table'[Type2] )
        ),
        _tar
    )

Capture.PNG

 

Regards,

Frank

 

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

View solution in original post

6 REPLIES 6
Community Support Team
Community Support Team

Re: Total average

Hi @JesperTim,

 

Could you please share your sample data and excepted result to me, if you don't have confidential data? Please upload your file to One Drive and share the link here.

 

Regards,

Frank

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

Re: Total average

Hi Frank

 

Sorry for my late reply 

 

The real data is confidential, therefore I made a quick BI with sample data, and tried to build up almost the same scenario.

Link to BI Test data

 

There is pictures which is in the link also showing my expected results. 

 

e.g. (Red is the current result and green is what I am trying to get)

Average of B53 TE Core in the BI is: ((Product FL value 15,75+ Product FU value 16,75)/2) = 16,07

I were hoping to get the following result instead: 15,75+16,75 = 32,50

 

Looking forward to receiving you answer. 

 

JesperTim Frequent Visitor
Frequent Visitor

Re: Total average

Hi Frank
Have you had the time to look into this?

 

Thanks.

Community Support Team
Community Support Team

Re: Total average

Hi @JesperTim,

 

Sorry for my late respond. I can work part of this out. As I cannot understand the logic of the week11 and week12. Could you please share more information about that? Also please check the steps as below.

 

1. Create a calculated table and create relationship between it and the fact table.

 

Table = DISTINCT(UNION(VALUES('Data list'[Type2]),VALUES('Data list'[Type3])))

rela.PNG

 

2. Create a measure to get the target.

 

Target1 = 
VAR _tar =
    CALCULATE ( SUM ( 'Data list'[Target] ) )
RETURN
    IF (
        ISBLANK ( _tar ),
        CALCULATE (
            SUM ( 'Data list'[Target] ),
            USERELATIONSHIP ( 'Data list'[Type2], 'Table'[Type2] )
        ),
        _tar
    )

Capture.PNG

 

Regards,

Frank

 

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

View solution in original post

Community Support Team
Community Support Team

Re: Total average

Hi @JesperTim,

 

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

 

Regards,
Frank

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

Re: Total average

Hi Frank

 

Thank you for your answer which solves my Target issue, but actuatlly the fix I were looking for was the Week 11 and 12 problem, which I now can see why you dont understand.

 

Week 11 is in fact Month 11 in my example and week 12 is Month 12.

And what I were looking for is:

Averages per item in that month, totaled by type or type name e.g. 

 

B53 Box 1 avg. = 19,29 +

B53 Box 2 avg. = 15,67 +

B53 Box 3 avg = 11,25 +

B53 Box 4 avg = 17,46 +

B53 Box 5 avg = 15,17 +

B53 Box 6 avg = 18,39

Type: B53 Shell (Box 1 - 6, (19,29+15,67+11,25+17,46+15,17+18,39)) = 97,23 

B53 FL = 18,50

B53 FU = 15,17

Type: B53 TE Core (FL+FU, (18,50 + 15,17)) = 37,20

Type name: B53 (All) = 97,23 + 37,20 =134,43

 

Above numbers are result from month 12

After this I will then need to compare these result toward the target and compare how the result is compared to previus months. 

 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 372 members 3,874 guests
Please welcome our newest community members: