Urgent! Question of dax switch fuction

[xiumi_hou]

Dear alL,

I want to create a table,

The first column is 1,2,3,4,

The second column is my switch funtion according to 1,2,3,4

(Second column is data type is percentage)

I have my measure 1,2,3,4, and I want to return the percentage of each. Ex. 1/(1+2+3+4)

However, when I put the two columns together, the percentage all show 100%...

Anyone know how I can adjust it? Thank you 

[edhans]

Can you post a sample of your data and what the expected result is? I cannot tell what is in your second column. You said it is a switch function, a percentage, and them mention a measure, and measures don't go in columns, so I am not sure what your data set looks like.

See this article on what you need to post to help us help you get a good answer.

[xiumi_hou]

Hi Please see my measure:

I have three table. 

1,Case table with column  case ID/received date/ closed date./closed reason(I have a,b,c,d total for four clousre reason) 

* Some of the case not closed yet, so no closed date and closure reason

2,Date table (My filter for this entire report is using date table), the connection of the table is received date with date table

I want to create a table like this: 

When I select Dec1-Dec 31, I want to show percentage of closed case by closure reason (the closed date should within period)

Column 1(close reason)    Column 2(Percentage of each closure reason)

a                                        20%

b                                        30%

c                                         20%

d                                         30%

Total                                  100%

Can you help me build the function? Thank you  @edhans 

[amitchandak]

If you create a graph close by reason. Then you can % of total from by right click at the measure in visual. Now how to have close date and receive date refer my blog on a similar line

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[xiumi_hou]

Hi @amitchandak  If it's a table.....what should I do? When I put all the percentage into one table.... all of it show 100% for a/b/c/d..Thank you 

[v-juanli-msft · Accepted solution]

Hi @xiumi_hou 

Create a date table without relationship with others,

Add "date" column from this "date" table to a slicer,

Create measures

mindate = MIN('date'[Date])

maxdate = MAX('date'[Date])

all in period = CALCULATE(COUNT('Table'[id]),FILTER(ALLSELECTED('Table'),'Table'[closed date]<=[maxdate]&&'Table'[closed date]>=[mindate]))

per reason =
CALCULATE (
    COUNT ( 'Table'[id] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[close reason] = MAX ( 'Table'[close reason] )
            && 'Table'[closed date] <> BLANK ()
            && 'Table'[closed date] <= [maxdate]
            && 'Table'[closed date] >= [mindate]
    )
)


percentage = [per reason]/[all in period]

modified % = IF(HASONEVALUE('Table'[close reason]),[percentage],SUMX(ALL('Table'[close reason]),[percentage]))

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Can you provide the formula for close formula. Because you have receive date for case receive and there are open . So what is close %. and are you looking for % of Total ?

To deal with these kind of case, I have one blog

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example. If possible please share a sample pbix file after removing sensitive information.
Thanks.

My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[xiumi_hou]

Hi Please see my measure:

I have three table. 

1,Case table with column  case ID/received date/ closed date./closed reason(I have a,b,c,d total for four clousre reason) 

* Some of the case not closed yet, so no closed date and closure reason

2,Date table (My filter for this entire report is using date table), the connection of the table is received date with date table

I want to create a table like this: 

When I select Dec1-Dec 31, I want to show percentage of closed case by closure reason (the closed date should within period)

Column 1(close reason)    Column 2(Percentage of each closure reason)

a                                        20%

b                                        30%

c                                         20%

d                                         30%

Total                                  100%

Can you help me build the function? Thank you  @amitchandak