cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dobregon Member
Member

Calculate the median of diferent sum of products of customers

Hi,

 

I'm trying to calculate the median from a sum of values and i have some problems. Exmaple to understad my problem.

 

  1. I have this table of values
    CustomerProductValue
    A127
    A227
    A350
    A442
    A528
    A627
    A730
    B126
    B211
    B348
    B418
    C150
  2. I want to sum the column [Value] for each customer and then with the sums of A, B, C then calculate the median of the 3 values. like the next example
    CustomerSum and then median
    A231
    B103
    C50
    Total (median of the 3 values)103


  3.  Then I want to have the 103 in each row as:
    CustomerSum and then medianTotal Median of sum
    A231103
    B103103
    C50103
    total103103

I'm trying with summarize, medianX but I didn't get  the median after the individual sum. Can someone help me on this?

 

Kind regards!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Calculate the median of diferent sum of products of customers

@dobregon @EugenioJunior 

 

This measure works with the sample data in a Table Visual

 

Median =
MEDIANX (
    CALCULATETABLE (
        VALUES ( Table1[PRODUCT ID] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[Customer] = SELECTEDVALUE ( Table1[Customer] )
        )
    ),
    CALCULATE ( SUM ( Table1[Value] ) )
)

median.png

Try my new Power BI game Cross the River

View solution in original post

6 REPLIES 6
Super User
Super User

Re: Calculate the median of diferent sum of products of customers

@dobregon 

 

You can use this MEASURE

 

Sum and then Median =
IF (
    HASONEFILTER ( Table1[Customer] ),
    SUM ( Table1[Value] ),
    MEDIANX ( VALUES ( Table1[Customer] ), CALCULATE ( SUM ( Table1[Value] ) ) )
)

and this one

 

Total Median of Sum =
MEDIANX (
    ALLSELECTED ( Table1[Customer] ),
    CALCULATE ( SUM ( Table1[Value] ) )
)
Try my new Power BI game Cross the River
EugenioJunior Frequent Visitor
Frequent Visitor

Re: Calculate the median of diferent sum of products of customers

@dobregon 

see this solution

SUM = 
MEDIANX(
    VALUES('Table1'[Customer]),
    CALCULATE(
                SUM('Table1'[Value])
                          )
                  )



MEDIANX = 
MEDIANX(
    ALL('Table1'[Customer]),
    CALCULATE(
                SUM('Table1'[Value])
                           )
                   )



Capture.PNG

dobregon Member
Member

Re: Calculate the median of diferent sum of products of customers

Thanks @EugenioJunior  and @Zubair_Muhammad  for the responses.

 

I have tried both solutions in the example and they work, but i have tried in my system and i can't use them. This is a better example that I'm trying to explain.

 

CustomerPRODUCT TYPE IDPRODUCT IDDATEValue
A111/1/2019977
A111/2/2019387
A221/1/2019929
A221/2/20191139
A531/1/2019402
A531/2/2019159
A541/1/2019344
A541/2/2019586
B151/1/20191261
B151/2/2019504
B261/1/2019295
B261/2/2019171
B571/1/20191086
B571/2/2019846
B581/1/20191209
B581/2/2019793
B591/1/2019102
B591/2/2019662
C1101/1/2019984
C1101/2/2019861
C2111/1/2019329
C2121/2/2019136
C5131/1/2019531
C5131/2/2019482

 

So as you can see, I have 3 Customers (A,B,C) and diferent type of products (imagen cars, bikes, boats) and for each type we can have 1 or more products inside the product type.

I have a page with slicers filters of dates and product type in order to analyze depends on the dates or product type that i have selected in the slicers.

 

As an example i have filter 1 and 2nd of Jan 2019 and the product type 5 in the slicers and i want to calculate something like that

 

Slicer dates filter the period, but the query Sum, median need to take only product type 5
CUSTOMERPRODUCTIDSUMMedian
A3561745.5
A4930745.5
B719321932
B820021932
B97641932
C1310131013

 

As you see, I want (for the slicers selected) the customer, different products for the product type selected in the slicer and then the sum of values for the dates selected and then other column with the median of the products for each customer.

 

For them calculate the dev something like 

 

CUSTOMERPRODUCT IDSUMMedian% (median/sum)
A3561745.5133%
A4930745.580%
B719321932100%
B82002193297%
B97641932253%
C1310131013100%

 

I have tried in powerbi and i always receive the sum of values instead of medians when i use the both formulas

 

 

 

Medians.JPG

 

the SUMfrom @EugenioJunior and @Zubair_Muhammad do the median in the total for the 3 customers not for each customer (i dont want a total value so, for me is not important)

 

And the median put the median for each customer but with the median of the total customers, but i want to have the median for each customer repetead in each productid for each customer.

 

Could you help me?

Thanks in advance!!

EugenioJunior Frequent Visitor
Frequent Visitor

Re: Calculate the median of diferent sum of products of customers

Hi, @dobregon 

 

What visual are you using?


Capture.PNG


MEDIANX2 = 
MEDIANX(Values('Table2'[PRODUCT ID]),
SUMX(Values('Table2'[PRODUCT ID]),
    CALCULATE(
               MEDIAN(Table2[Value])
            ) 
    )
        )



Does that help?
If you do not help, tell me.

 

 

 

 

 

 

Super User
Super User

Re: Calculate the median of diferent sum of products of customers

@dobregon @EugenioJunior 

 

This measure works with the sample data in a Table Visual

 

Median =
MEDIANX (
    CALCULATETABLE (
        VALUES ( Table1[PRODUCT ID] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[Customer] = SELECTEDVALUE ( Table1[Customer] )
        )
    ),
    CALCULATE ( SUM ( Table1[Value] ) )
)

median.png

Try my new Power BI game Cross the River

View solution in original post

Highlighted
dobregon Member
Member

Re: Calculate the median of diferent sum of products of customers

Thanks a lot @Zubair_Muhammad , this measure works good! and thanks @EugenioJunior  for take your time to help me!

 

I didn't know function calculated table.

 

Kind regards!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 384 members 4,612 guests
Please welcome our newest community members: