Forum Discussion
Helper VTreemap - dynamic grouping question.
Helper VHello 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'))
Helper VOnce 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
Hello Hardik,
Were you able to reproduce the problem? Please let me know. Thanks.