Calculated dimension based on value

[Anonymous]

HI,

I have the following source dataset.

Date	Employee name	Project name	Client billable	Internal Product billable	Utilization hours(non Billable)
17-05-2019	A	amitek	30	0	10
17-05-2019	B	Malvern	40		0
17-05-2019	C	Kendal	0	40	0
17-05-2019	D	Quva	10	0	40

A employee :
Client billable hours value less than 40 and greater than 0, it falls the category like Partially client billable
utilization hours less than 40, it falls the category like partially non-billable

B employee:
Fully client billable

C Employee:
Fully Internal billable

D Employee:
Partially Client Billable, Fully non-billable

Particular employee falls in many categories.

I want  to show the categories in a pie chart

How can I achieve this one?

Note: Employee name is unique

I have the one-week data set only, for one week target working hours is 40. based on that value we need to categorize the billing status

[v-cherch-msft]

Hi @Anonymous 

You may use 'Unpivot column' for Client billable and 'Utilization hours(non Billable)' columns in query editor first.Then create a column with IF condition.Use the column as legend and create a measure to get the count.For example:

Column = 
IF (
    'Table'[Internal Product billable] = 40,
    "Fully Internal billable",
    IF (
        'Table'[Attribute] = "Client billable",
        IF (
            'Table'[Value] < 40
                && 'Table'[Value] > 0,
            "Partially client billable",
            IF ( 'Table'[Value] = 40, "Fully client billable" )
        ),
        IF (
            'Table'[Attribute] = "Utilization hours(non Billable)",
            IF (
                'Table'[Value] < 40
                    && 'Table'[Value] > 0,
                "partially non-billable",
                IF ( 'Table'[Value] = 40, "Fully non-billable" )
            )
        )
    )
)

Regards,

[Anonymous]

Hi,

I suppose, your suggestion won't work here.
Below is my source dataset,

I'm running the summarize DAX query on the above table to create another table, the structure of which is above post, however, this table is not being available in the power query window. Please suggest.

Every  Friday, data are inserted to source Dataset.

if Suppose my Source dataset look following as(In future my source dataset look like the following format),

How to categorize the Fully client billable, Partially client billable, Fully internally billable, Partially internally billable, Non-billable

Note: take the target hour as per week 40

[Anonymous]

Hi,

please find the Pbix file,

https://avacorp1-my.sharepoint.com/:u:/g/personal/yuvaraj_g_avasoft_com/EaI2K62bvrlGm585UplQPTgBgvEJ...

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You may use DAX to create the unpivot table.Then use the workaround as above.Check Table 2 in sample file.

Table 2 = 
UNION (
    SELECTCOLUMNS (
        'Data',
        "Date", 'Data'[Date],
        "Name", 'Data'[Employee name],
        "Internal Product billable", 'Data'[Internal Product billable],
        "Attribute", 'Data'[Column],
        "Value", 'Data'[Client billable]
    ),
    SELECTCOLUMNS (
        'Data',
        "Date", 'Data'[Date],
        "Name", 'Data'[Employee name],
        "Internal Product billable", 'Data'[Internal Product billable],
        "Attribute", 'Data'[Column 2],
        "Value", 'Data'[Utilization hours(non Billable)]
    )
)

Regards,

[Anonymous]

Hi @v-cherch-msft,

Based on your suggestion I achieved the scenario.

but I have a problem in a Pie chart.  if I click the last one month, last one year in relative date filter,

that piechart categorization not showing the correct data.

because My requirement is,

Based on the measure, dimension needs to change dynamically in a pie chart.

Based on the below URL suggestion, I achieved the scenario.

but I have the problem in the association.

https://community.powerbi.com/t5/Desktop/Output-Status-as-Column/td-p/692021/highlight/false

Please find the sample file as the following URL.

https://avacorp1-my.sharepoint.com/:u:/g/personal/yuvaraj_g_avasoft_com/EWYmoDlZIS9IvtIZj1QgDGsBIed7...

Any help would be appreciated

Thanks in advance

Yuvaraj.

[v-cherch-msft]

Hi @Anonymous 

I would suggest you create a new thread on forum so that more community members can see it and provide advice. Please remember to post dummy data and desired result.

Regards,