cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

CREATE SUMMARY MEASURE FROM A TABLE ALSO USING AVERAGE

I have the following table:

 

table.png

I need a measure that can do the following circumstances:

 

1 - First I need to calculate the average monthly sales per user as shown in the following picture. Dividing the total revenue for n months that the specified user had revenue. Except that I need that also considering supplier ID

Note:

- Revenue is the sum aggregation for the REVENUE column.

circ1.png

 

 

2 - I need the same calculation as the previous circumstance but now only considering supplier (an AllExcept should do this part).

circ2.png

 

 

3 - The final result should be as shown in the following picture:

circ3.png

 

The "USER_RESULT" would be the result of the n.1 circumstance, and the "SUPLIER_RESULT" would be the n.2.

 

 

I know that by using AVERAGE and SUMMARY one can probably do this very easily, but I haven't found a way to solve this problem yet.

 

How can I create a measure to display the final result?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: CREATE SUMMARY MEASURE FROM A TABLE ALSO USING AVERAGE

Hi @Caldasls15_ ,

You can create a measure to get the average revenue based on the supplier, then drag the fields USER,SUPPLIER_ID,REVENUE and new created measure onto Table visual just like below screen shot.

SUPPLIER_RESULT = 
VAR countofSMonth =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[MONTH] ),
        ALLEXCEPT ( 'Table', 'Table'[SUPPLIER_ID] )
    )
VAR RevenueofpSupplier =
    CALCULATE (
        SUM ( 'Table'[REVENUE] ),
        ALLEXCEPT ( 'Table', 'Table'[SUPPLIER_ID] )
    )
RETURN
    DIVIDE ( RevenueofpSupplier, countofSMonth )

CREATE SUMMARY MEASURE FROM A TABLE ALSO USING AVERAG.JPG

Best Regards

Rena

View solution in original post

2 REPLIES 2
Highlighted
Microsoft
Microsoft

Re: CREATE SUMMARY MEASURE FROM A TABLE ALSO USING AVERAGE

Hi @Caldasls15_ ,

You can create a measure to get the average revenue based on the supplier, then drag the fields USER,SUPPLIER_ID,REVENUE and new created measure onto Table visual just like below screen shot.

SUPPLIER_RESULT = 
VAR countofSMonth =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[MONTH] ),
        ALLEXCEPT ( 'Table', 'Table'[SUPPLIER_ID] )
    )
VAR RevenueofpSupplier =
    CALCULATE (
        SUM ( 'Table'[REVENUE] ),
        ALLEXCEPT ( 'Table', 'Table'[SUPPLIER_ID] )
    )
RETURN
    DIVIDE ( RevenueofpSupplier, countofSMonth )

CREATE SUMMARY MEASURE FROM A TABLE ALSO USING AVERAG.JPG

Best Regards

Rena

View solution in original post

Regular Visitor

Re: CREATE SUMMARY MEASURE FROM A TABLE ALSO USING AVERAGE

Hey, 

 

Still on this subject, what measure can I do if I want a column to show the total of the "SUPPLIER_RESULT" repeatedly in each row? 

 

I thought of a ALL_SELECTED but since it's a measure, I can't reference it.

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors