Forum Discussion
Helper VTreemap - dynamic grouping question.
Helper VIs there a way to share .pbix file? If so, I can send you my file to have you show me what I am missing.
This is how my data in table format looks like in power bi. As you can see, the table format looks good. However when I try to create treemap with "15D TAT (%)" and colorcode selected, then the colorcode directly goes to tooltip and not in other visualization (e.g. values or details). As soon as select the Health Plan also in it, then I get the 2nd level treemap where each box represents the health plan.
I am stumped about what is missing.
Continued Contributor- ppgandhi11
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
Hi Hardik,
I had been exploring option to attach and send you the file. Unfortunately the onedrive, dropbox etc is blocked at my work place. So we will need to go to longer path. I am sending you the complete insert statements that I am running along with the calculation I am performing with screenshot. Hopefully you can recreate the problem at your end.
For creating table:
create table testtbl (healthplan varchar(20), claimkey varchar(20), receiveddt datetime, reportdate datetime)
--
INSERT into testtbl VALUES (N'ABC', N'111', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'ABC', N'222', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-10 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'ABC', N'333', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-12 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'ABC', N'444', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-13 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'DEF', N'555', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-20 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'DEF', N'666', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-10 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'DEF', N'777', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-12 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'DEF', N'888', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-13 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'GHI', N'999', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-20 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'GHI', N'000', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-30 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'GHI', N'123', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-12 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'GHI', N'234', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-13 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'345', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'456', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'567', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'678', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'131', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'132', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'133', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'134', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'135', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'136', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'137', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'138', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'146', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-20 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'147', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-20 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'139', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'140', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'141', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'142', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'143', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'144', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'145', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'MNO', N'124', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-20 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'MNO', N'125', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-30 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'MNO', N'126', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-25 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'MNO', N'127', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
--
- ppgandhi11
Hello Hardik,
Sorry for the delayed response. Unfortunately Ondrive and Dropbox are blocked at my workplace. I had been trying to get around it. so we will need to go to longer path. I am sending you all the necessary info to recreate the problem.
create table testtbl (healthplan varchar(20), claimkey varchar(20), receiveddt datetime, reportdate datetime)
INSERT into testtbl VALUES (N'ABC', N'111', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'ABC', N'222', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-10 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'ABC', N'333', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-12 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'ABC', N'444', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-13 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'DEF', N'555', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-20 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'DEF', N'666', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-10 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'DEF', N'777', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-12 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'DEF', N'888', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-13 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'GHI', N'999', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-20 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'GHI', N'000', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-30 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'GHI', N'123', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-12 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'GHI', N'234', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-13 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'345', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'456', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'567', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'678', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'131', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'132', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'133', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'134', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'135', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'136', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'137', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'138', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'146', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-20 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'147', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-20 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'139', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'140', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'141', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'142', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'143', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'144', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'JKL', N'145', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'MNO', N'124', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-20 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'MNO', N'125', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-30 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'MNO', N'126', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-25 00:00:00.000' AS DateTime))
INSERT into testtbl VALUES (N'MNO', N'127', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))
- ppgandhi11
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
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
Hello Hardik,
Were you able to reproduce the problem? Please let me know. Thanks.