Creating a Table visual in Power Bi

[Anonymous]

I want to create a table visual in Power Bi like no. of accounts and Delay Payments(in no. of days). 

What if I want something like.

Delay Payments(in days)              No. of Accounts

0+                                                 5000

30+                                                3800

60+                                                2700

90+                                                2300

So on... Is it possible in Power Bi

[FreemanZ]

hi @Anonymous 

Supposing you have the following data:

ActualDate	DueDate	Gap	Amt
10/5/2022	10/1/2022	-4	10
10/28/2022	11/1/2022	4	50
11/30/2022	12/1/2022	1	70
12/26/2022	1/1/2023	6	100
1/31/2023	2/1/2023	1	10
2/20/2023	3/1/2023	9	90
3/22/2023	4/1/2023	10	90
4/27/2023	5/1/2023	4	10

To get what you expect, you would need to:

1) create another segmentation table from Exel or with DAX, like this:

GapSegment	Min	Max
<0	-5	-1
0-5	0	5
6-10	6	10

(Note: no need to relate the tables.)

2) Write a measure like this:

SegAmt = 
VAR _min = MIN(segment[Min])
VAR _max = MAX(segment[Max])
RETURN
CALCULATE(
    SUM(data[Amt]),
    data[Gap]>=_min
        && data[Gap] <=_max
)

3) plot the measure with the  [GapSegment] column in a table visual. 

I tried and it worked like this:

[FreemanZ · Accepted solution]

Hi @Anonymous 

just noticed you wanted a cummulative total, then 

Supposing you have the following data:

ActualDate	DueDate	Gap	Amt
10/5/2022	10/1/2022	-4	10
10/28/2022	11/1/2022	4	50
11/30/2022	12/1/2022	1	70
12/26/2022	1/1/2023	6	100
1/31/2023	2/1/2023	1	10
2/20/2023	3/1/2023	9	90
3/22/2023	4/1/2023	10	90
4/27/2023	5/1/2023	4	10

To get what you expect, you would need to:

1) create another segmentation table named segment2 from Exel or with DAX, like this:

GapSegment	Min	Max
<0	-5	-1
<5	-5	5
<10	-5	10

(Note: no need to relate the tables.)

2) Write a measure like this:

Seg2Amt = 
VAR _min = MIN(segment2[Min])
VAR _max = MAX(segment2[Max])
RETURN
CALCULATE(
    SUM(data[Amt]),
    data[Gap]>=_min
        && data[Gap] <=_max
)

3) plot the measure with the  [GapSegment] column in a table visual. 

I tried and it worked like this:

So you see, the point is to create an independent segmentation table. 

[Anonymous]

Thanks a Lot. Worked Flawlessly and Amazing Explaination. 

[Mahesh0016]

@Anonymous Create Calculated Column Days.

Days = DATEDIFF(Dim_Date[Date],MAX(Dim_Date[Date]),DAY)

Then

Delay Payments = SWITCH(TRUE(),Dim_Date[Days]>=0 && Dim_Date[Days]<30,"0+",

Dim_Date[Days]>=30 && Dim_Date[Days]<60,"30+",

Dim_Date[Days]>=60 && Dim_Date[Days]<90,"60+",

Dim_Date[Days]>=90 && Dim_Date[Days]<120,"90+",

Dim_Date[Days]>=120 && Dim_Date[Days]<150,"120+",

Dim_Date[Days]>=150 && Dim_Date[Days]<180,"150+",

Dim_Date[Days]>=180 && Dim_Date[Days]<210,"180+",

Dim_Date[Days]>=210 && Dim_Date[Days]<240,"210+",

Dim_Date[Days]>=240 && Dim_Date[Days]<270,"240+",

Dim_Date[Days]>=270 && Dim_Date[Days]<300,"270+",

Dim_Date[Days]>=300 && Dim_Date[Days]<330,"300+",

Dim_Date[Days]>=300 && Dim_Date[Days]<330,"300+","360+")

If this post helps, please consider accept as solution to help other members find it more quickly.

[Anonymous]

Hello Mahesh, Thanks for replying. But this isn't what I was looking for. This will just create slabs/bins/groups. The particular value is part of a single group.

But in the case I am looking for...

0+ All the values except 0 will be considered.

30+ All the values greater than 30 will be considered 

But in the case you have mentioned. 0-30 will be considered and 31-60 will be considered.

[Mahesh0016]

@Anonymous Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[amitchandak]

@Anonymous , if days is in column you can create a new column

Switch(True() ,

[Days] <30 , "  0+",

[Days] <60 , "  30+",

[Days] <90 , "  60+",

"90+"

)

And use this new column in visual along with other measures

if days is measure, then you need Dynamic Segmentation

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

[Anonymous]

Hello amitchandak, Thanks for replying. But this isn't what I was looking for. This will just create slabs/bins/groups. The particular value is part of a single group.

But in the case I am looking for...

0+ All the values except 0 will be considered.

30+ All the values greater than 30 will be considered 

But in the case you have mentioned. 0-30 will be considered and 31-60 will be considered.

[amitchandak]

@Anonymous, As these value, are not mutually exclusive, first create a measure for all of them and then create a calculation group or use Switch values on row in matrix 

0+ = calculate([Meausre], filter(Table, Table[Age] >0))

30+ = calculate([Meausre], filter(Table, Table[Age] >30))

Matrix option

 

 

Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0