Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a table like :
Date CustomerID TotalPrice
and I have create 3 measures :
1. Sum of total price
2. Count of distinct customer
3. avarege - measure 1 / measure 2
When I use it on a chart with yearmonth on the X axis everything works fine.
But I want to sum that avg for a year view.
I mean if I have on
jan2018 -avg= 100
feb18 - avg =200
march18 - avg =250
I will want to see on 2018 sumavg 550
if I just put the year on the X axis it will make avg for the all year and not only by month
its make a problem because i have diffrent number of customers each month and it will not reflect the real snapshot for my bussiness.
Thanks for your help.
Solved! Go to Solution.
Hello,
you could try to SUMMARIZE and SUMX:
YearAvg= SUMX( SUMMARIZE(Table,
[YearColumn],
[MonthColumn],
"Sum of total price",SUM([Price],
"CountOfCustomer",DISTINCTCOUNT([CustomerID])),
[Sum of total price]/[CountOfCustomer])
Best regards
Hello,
you could try to SUMMARIZE and SUMX:
YearAvg= SUMX( SUMMARIZE(Table,
[YearColumn],
[MonthColumn],
"Sum of total price",SUM([Price],
"CountOfCustomer",DISTINCTCOUNT([CustomerID])),
[Sum of total price]/[CountOfCustomer])
Best regards
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |