DISTINCTCOUNT and then including Median function in same DAX

[harirao]

Hi Team,
I want to create a measure/column, on distinctcount & median together
Eg: created in Excel


Is this possible in power bi.

Regards,

Hari 

[v-alq-msft · Accepted solution]

Hi, @harirao 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create measures as below.

DisctinctCount OId = DISTINCTCOUNT('Table'[OpportunityId])
actual_visibility_weeks measure 1 = 
MEDIANX(
    DISTINCT('Table'[actual_visibility_weeks]),
    'Table'[actual_visibility_weeks]
)
actual_visibility_weeks measure 2 = 
IF(
    ISFILTERED('Table'[ID]),
    SUM('Table'[actual_visibility_weeks]),
    MEDIANX(
        ALL('Table'),
        'Table'[actual_visibility_weeks]
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-alq-msft · Accepted solution]

Hi, @harirao 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create measures as below.

DisctinctCount OId = DISTINCTCOUNT('Table'[OpportunityId])
actual_visibility_weeks measure 1 = 
MEDIANX(
    DISTINCT('Table'[actual_visibility_weeks]),
    'Table'[actual_visibility_weeks]
)
actual_visibility_weeks measure 2 = 
IF(
    ISFILTERED('Table'[ID]),
    SUM('Table'[actual_visibility_weeks]),
    MEDIANX(
        ALL('Table'),
        'Table'[actual_visibility_weeks]
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[harirao]

Hi Allan @v-alq-msft, 

Thanks for providing the solution, after creating discount and medianx, need to create a table region wise and include drill down option as mentioned  below
please note dashboard will be refreshed every weeks, filter for weekly view.

Regards,

Hari 

[v-alq-msft · Accepted solution]

Hi, @harirao 

You may create a calculated table as below.

Table 2= 
SUMMERIZE(
    'Table',
    'Table'[subregion],
    "Median",
    VALUE(MEDIAN('Table'[actual_visibility_weeks]))
)

Here is my data for test.

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[harirao]

Hi Allan @v-alq-msft,

Thanks for the above solution, by using this i was able to create measure, now i want to include two condition inside the DAX

1st Condition : [actual_visibility_Weeks](>1) should be 1 and above. Highlighted in green
2nd Condition : [actual_visibility_Weeks]=0 &  [0 Visibility exclusion] = "No". Should be included in highlighted in red

Median = FORMAT(MEDIANX(

SUMMARIZE(Query1,Query1[subregion],Query1[actual_visibility_Weeks],Query1[OpportunityId],"Viswks",

COUNT(Query1[OpportunityId])),

[actual_visibility_Weeks]),0)

Can you please help me to include this conditions in my DAX.
Thank you

Regards,
Hari

[v-alq-msft]

Hi, @harirao 

You may create a measure as below.

Measure = 
IF(
    SELECTEDVALUE('Table'[actual_visibility_weeks])>0,
    "green",
    IF(
        SELECTEDVALUE('Table'[actual_visibility_weeks])=0,
        "red"
    )
)

Then you can click 'subregion'=>'conditional formatting'=>'background color', set as below.

Result:

Best Regards

Allan

[d_gosbell]

It should be possible. It's hard to be exact with just a screenshot of data, but you should be able to use MEDIANX over the values of the week numbers.

= MEDIANX( values( table[1] ) , [actual_visibility_weeks] )

But I don't really understand what you mean by your comment of needing to consider the opportunityId as your example excel function is only looking at column B. This may change the first parameter of the MEDIANX as this controls the grain at which the median is calculated.