Forum Discussion

ppgandhi11's avatar
ppgandhi11
Helper V
8 years ago

Treemap - dynamic grouping question.

Hi,   I have a treemap that has supplies category, each colored either green (if the sales for that supplies category is above 90%), or red (if the sales is below 90%). So, my treemap has bunch of ...
Need Help
ppgandhi11's avatar
ppgandhi11
Helper V
8 years ago

This link says there is no functionality to attach the file. We need to use dropbox or onedrive etc to share the content. Don't know how to go about it.

ppgandhi11's avatar
ppgandhi11
Helper V
8 years ago

Hello Hardik,

Sorry for the late response. Onedrive and dropbox are blocked at my workplace, I had been trying to get around it but it seems there is no option for sharing the file.
So we will need to go longer route. I am sending  you all the details to reproduce the problem.
Please create table as below.

create table testtbl (healthplan varchar(20), claimkey varchar(20), receiveddt datetime, reportdate datetime)

INSERT into testtbl VALUES ('ABC', '111', ('2018-01-01'), ('2018-01-02'))

INSERT into testtbl VALUES ('ABC', '222', ('2018-01-01'), ('2018-01-10'))

INSERT into testtbl VALUES ('ABC', '333', ('2018-01-01'), ('2018-01-12'))

INSERT into testtbl VALUES ('ABC', '444', ('2018-01-01'), ('2018-01-13'))

INSERT into testtbl VALUES ('DEF', '555', ('2018-01-01'), ('2018-01-20'))

INSERT into testtbl VALUES ('DEF', '666', ('2018-01-01'), ('2018-01-10'))

INSERT into testtbl VALUES ('DEF', '777', ('2018-01-01'), ('2018-01-12'))

INSERT into testtbl VALUES ('DEF', '888', ('2018-01-01'), ('2018-01-13'))

INSERT into testtbl VALUES ('GHI', '999', ('2018-01-01'), ('2018-01-20'))

INSERT into testtbl VALUES ('GHI', '000', ('2018-01-01'), ('2018-01-30'))

INSERT into testtbl VALUES ('GHI', '123', ('2018-01-01'), ('2018-01-12'))

INSERT into testtbl VALUES ('GHI', '234', ('2018-01-01'), ('2018-01-13'))

INSERT into testtbl VALUES ('JKL', '345', ('2018-01-01'), ('2018-01-02'))

INSERT into testtbl VALUES ('JKL', '456', ('2018-01-01'), ('2018-01-02'))

INSERT into testtbl VALUES ('JKL', '567', ('2018-01-01'), ('2018-01-02'))

INSERT into testtbl VALUES ('JKL', '678', ('2018-01-01'), ('2018-01-02'))

INSERT into testtbl VALUES ('JKL', '131', ('2018-01-01'), ('2018-01-02'))

INSERT into testtbl VALUES ('JKL', '132', ('2018-01-01'), ('2018-01-02'))

INSERT into testtbl VALUES ('JKL', '133', ('2018-01-01'), ('2018-01-02'))

INSERT into testtbl VALUES ('JKL', '134', ('2018-01-01'), ('2018-01-02'))

INSERT into testtbl VALUES ('JKL', '135', ('2018-01-01'), ('2018-01-02'))

INSERT into testtbl VALUES ('JKL', '136', ('2018-01-01'), ('2018-01-02'))

INSERT into testtbl VALUES ('JKL', '137', ('2018-01-01'), ('2018-01-02'))

INSERT into testtbl VALUES ('JKL', '138', ('2018-01-01'), ('2018-01-02'))

INSERT into testtbl VALUES ('JKL', '146', ('2018-01-01'), ('2018-01-20'))

INSERT into testtbl VALUES ('JKL', '147', ('2018-01-01'), ('2018-01-20'))

INSERT into testtbl VALUES ('JKL', '139', ('2018-01-01'), ('2018-01-02'))

INSERT into testtbl VALUES ('JKL', '140', ('2018-01-01'), ('2018-01-02'))

INSERT into testtbl VALUES ('JKL', '141', ('2018-01-01'), ('2018-01-02'))

INSERT into testtbl VALUES ('JKL', '142', ('2018-01-01'), ('2018-01-02'))

INSERT into testtbl VALUES ('JKL', '143', ('2018-01-01'), ('2018-01-02'))

INSERT into testtbl VALUES ('JKL', '144', ('2018-01-01'), ('2018-01-02'))

INSERT into testtbl VALUES ('JKL', '145', ('2018-01-01'), ('2018-01-02'))

INSERT into testtbl VALUES ('MNO', '124', ('2018-01-01'), ('2018-01-20'))

INSERT into testtbl VALUES ('MNO', '125', ('2018-01-01'), ('2018-01-30'))

INSERT into testtbl VALUES ('MNO', '126', ('2018-01-01'), ('2018-01-25'))

INSERT into testtbl VALUES ('MNO', '127', ('2018-01-01'), ('2018-01-02'))

  • ppgandhi11's avatar
    ppgandhi11
    Helper V
    8 years ago

    Once you create the table as requested earlier,

     

    Please select entire table into powerbi query. Now, create 2 New columns as:

     

    TAT = datediff(Testtbl[receiveddt].[Date], Testtbl[reportdate].[Date], day)

    15DTAT = if(Testtbl[TAT] < 15, 1, 0)

     

    Create New Measure as:

    15DTAT% = CALCULATE(SUM(Testtbl[15DTAT])/count(Testtbl[claimkey])*100)

     

    In the treemap, select:

    healthplan, color and 15DTAT%. This will show you every healthplan with its corresponding 15DTAT% value.

     

    What I am looking for is:

    In a treemap only 2 blocks - Green and Red. Green represents # of Healthplans having 15DTAT% >= 90. Red represents # of Healthplans having 15DTAT% < 90.

     

    In this case, the healthplan ABC has 100% and JKL 90.48% and hence Green color would be for 2 healthplans. DEF, GHI and MNO has < 90% and hence Red color would be for 3 healthplans.

     

    At my end, it appears as below:  

  • ppgandhi11's avatar
    ppgandhi11
    Helper V
    8 years ago

    Hello Hardik,

     

    Were you able to reproduce the problem? Please let me know. Thanks.