Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi I am new to powerBI. I am trying to calculate the average revenue in different strata like below (hypothetical data). I have a sales table for each state. I calculates ranks based on the sales, and I can individually create the measures based on the ranks, but I am not sure how to create a measure/table/column so that I can have a visual like this. Thank you for your help.
Strata | Average Sales | Median Sales |
Top 1-10 States | 55,000 | 46,000 |
Top 11-20 States | 45,000 | 44,000 |
Top 21-30 States | 10,000 | 11,000 |
Top 31-40 States | 5,000 | 4,000 |
Other States | 4000 | 3800 |
Solved! Go to Solution.
Hi @user10001 ,
Yes. Just change the rank measure like this:
rank =
VAR _rank =
RANKX (
FILTER (
ALL ( Sheet1 ),
'Sheet1'[Date] >= MIN ( DateSlicer[Date] )
&& 'Sheet1'[Date] <= MAX ( DateSlicer[Date] )
),
CALCULATE ( MAX ( ( 'Sheet1'[Revenue] ) ) ),
,
DESC,
SKIP
)
RETURN
IF (
MAX ( 'Sheet1'[Date] ) >= MIN ( DateSlicer[Date] )
&& MAX ( 'Sheet1'[Date] ) <= MAX ( DateSlicer[Date] ),
_rank,
BLANK ()
)
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
Hi @user10001 ,
Yes. Just change the rank measure like this:
rank =
VAR _rank =
RANKX (
FILTER (
ALL ( Sheet1 ),
'Sheet1'[Date] >= MIN ( DateSlicer[Date] )
&& 'Sheet1'[Date] <= MAX ( DateSlicer[Date] )
),
CALCULATE ( MAX ( ( 'Sheet1'[Revenue] ) ) ),
,
DESC,
SKIP
)
RETURN
IF (
MAX ( 'Sheet1'[Date] ) >= MIN ( DateSlicer[Date] )
&& MAX ( 'Sheet1'[Date] ) <= MAX ( DateSlicer[Date] ),
_rank,
BLANK ()
)
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
Hi @user10001 ,
Sorry to my late reply...
According to my understand, you want to display TOP X based on specific date ,right?
Firstly ,you need to create a date table for slicer ,then just change the rank measure like this:
rank =
VAR _rank =
RANKX (
FILTER ( ALL ( Sheet1 ), 'Sheet1'[Date] = SELECTEDVALUE ( DateSlicer[Date] ) ),
CALCULATE ( MAX ( ( 'Sheet1'[Revenue] ) ) ),
,
ASC
)
RETURN
IF (
MAX ( 'Sheet1'[Date] ) = SELECTEDVALUE ( DateSlicer[Date] ),
_rank,
BLANK ()
)
My visualization looks like this:
You could take a look at the pbix file here.
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
@v-eqin-msft Thank you. In your pbix file, when I put the date slicer as between two dates, it shows me blank table. Is it possible that I select the date range and it will show the rank like you have shown in the visual? I really appreciate your help in this.
Hi @user10001 ,
According to my understanding, you want to calculate the average and median based on rank, right?
You could follow these steps:
1.Enter data to create a new table
2.Use the following formulas:
rank =
RANKX (
ALL ( RevenueTable ),
CALCULATE ( MAX ( 'RevenueTable'[Sales] ) ),
,
DESC
)
avg =
SWITCH (
SELECTEDVALUE ( NewTable[RankTag] ),
"Top1-10", AVERAGEX ( FILTER ( RevenueTable, [rank] <= 10 ), [Sales] ),
"Top11-20",
AVERAGEX ( FILTER ( RevenueTable, [rank] > 10 && [rank] <= 20 ), [Sales] ),
"Top21-30",
AVERAGEX ( FILTER ( RevenueTable, [rank] > 20 && [rank] <= 30 ), [Sales] ),
AVERAGEX ( FILTER ( RevenueTable, [rank] > 30 ), [Sales] )
)
median =
SWITCH (
SELECTEDVALUE ( NewTable[RankTag] ),
"Top1-10", MEDIANX ( FILTER ( RevenueTable, [rank] <= 10 ), [Sales] ),
"Top11-20",
MEDIANX ( FILTER ( RevenueTable, [rank] > 10 && [rank] <= 20 ), [Sales] ),
"Top21-30",
MEDIANX ( FILTER ( RevenueTable, [rank] > 20 && [rank] <= 30 ), [Sales] ),
AVERAGEX ( FILTER ( RevenueTable, [rank] > 30 ), [Sales] )
)
My visualization looks like this:
Is the result what you want? If you have any questions, please upload some data samples and expected output.
Please do mask sensitive data before uploading.
Best Regards,
Eyelyn Qin
Hi @v-eqin-msft - Appreciate your help. But it still doesn't solve my need. I have the data with individual dates, and I need to have the date slicer to get the average and median revenue for specific ranktag for certain dates. The vissual you have is what I need, I just need to have the date slicer as well. I have attached the sample data I have.
https://drive.google.com/file/d/1c9vXl8LSEHyhRgIbifY_YYXNe9uGISJl/view?usp=sharing
Thank you for your help.
Hi @user10001
Do you have strata values listed in a separate column?
If so, just enter the measure := AVERAGE(Table [Sales]) and :=MEDIAN(Table [Sales]), and Power BI will automatically calculate the average for each group.
If you need any more help, please upload a sample .pbix file and I'll help.
_______________
If I helped, please accept the solution and give kudos! 😀
Hi lkalawski Thank you for your help. I don't have extra column. I created the rank measure based on the sales. Do I need to create one? If so, can you please let me know how I can do that.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |