creating dimension based on measure value

[Anonymous]

Hi All,

I want to show the final output in a pie chart 

• Dimension:% bucket 
• Measure:count (distinct ID)

I have two tables "Actual Hour Table" and "Target Hour Table".

Actual Hour Table:

Target Hours Table:

I have created Two Measure 

Target Achievement % = sum(Table1[Work Hour])/SUM('Target Hour'[Target Hour])

%bucket =
IF (Table1[Target Achievement %] = 1, "100%",
    IF ([Target Achievement %] < 1 && Table1[Target Achievement %] >= 0.75, "75% to 99%",
        IF ([Target Achievement %] < 0.75 && Table1[Target Achievement %] >= 0.5,"50% to 74%",
            IF ([Target Achievement %] < 0.5 && Table1[Target Achievement %] >= 0.25, "25% to 54%",
                IF ([Target Achievement %] < 0.25 && Table1[Target Achievement %] >= 0, "0% to 24%",
                    ">100%"
                )
            )
        )
    )
)

finally, I show the result like below image

Week + Employee ID view

Employee ID view

I want to show this result in Pie Chart 

if i select "04/05/2019" i want to show this result

if I select "04/12/2019" I want to show this result

if I select both dates,I want to show this result

Dataset 

WeekEndDate	EmpID	Work Hour	Weekly Target
4/5/2019	E1	25	40
4/12/2019	E1	25	40
4/5/2019	E2	30	40
4/12/2019	E2	50	40
4/5/2019	E3	40	40
4/12/2019	E3	40	40
4/5/2019	E4	20	40
4/12/2019	E4	0	40

[v-juanli-msft]

Hi @Anonymous 

Change measure to columns intead

Column_Target Achievement =
CALCULATE (
    SUM ( Table1[Work Hour] ),
    ALLEXCEPT ( Table1, Table1[WeekEndDate], Table1[EmpID] )
)
    / CALCULATE (
        SUM ( 'Target Hour'[Weekly Target] ),
        ALLEXCEPT ( Table1, Table1[WeekEndDate] )
    )

Column——%bucket = 
IF (Table1[Column_Target Achievement] = 1, "100%",
    IF (Table1[Column_Target Achievement]  < 1 && Table1[Column_Target Achievement]  >= 0.75, "75% to 99%",
        IF (Table1[Column_Target Achievement]  < 0.75 && Table1[Column_Target Achievement]  >= 0.5,"50% to 74%",
            IF (Table1[Column_Target Achievement]  < 0.5 && Table1[Column_Target Achievement]  >= 0.25, "25% to 54%",
                IF (Table1[Column_Target Achievement]  < 0.25 && Table1[Column_Target Achievement]  >= 0, "0% to 24%",
                    ">100%"
                )
            )
        )
    )
)

As tested, when selecting either 2019/4/5 or 2019/4/12, the pie chart works as expected.

But when select both 2019/4/5 and 2019/4/12, it shows as below

Based on my understanding, it should be correct.

But your expected visual seems beyond my understanding, could you kindly clear me?

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.