cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

creating dimension based on measure value

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:

Screenshot_1.png

Target Hours Table:

Screenshot_2.png

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

Screenshot_3.png

Employee ID view

Screenshot_4.png

I want to show this result in Pie Chart 

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

Screenshot_5.png

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

Screenshot_6.png

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

Screenshot_7.png

 

Dataset 

WeekEndDateEmpIDWork HourWeekly Target
4/5/2019E12540
4/12/2019E12540
4/5/2019E23040
4/12/2019E25040
4/5/2019E34040
4/12/2019E34040
4/5/2019E42040
4/12/2019E4040

 

1 REPLY 1
Community Support Team
Community Support Team

Re: creating dimension based on measure value

Hi @vengadeshpalani 

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

12.pngBased 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.

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: 293 members 3,276 guests
Please welcome our newest community members: