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

Highlighted
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

August Community Highlights

Check out a full recap of the month!

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