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
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
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
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors