Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have the following table:
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.
2 - I need the same calculation as the previous circumstance but now only considering supplier (an AllExcept should do this part).
3 - The final result should be as shown in the following picture:
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?
Solved! Go to Solution.
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 )
Best Regards
Rena
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 )
Best Regards
Rena
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
20 | |
18 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |