Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
anmolkatia
New Member

Creating a Table visual in Power Bi

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

1 ACCEPTED SOLUTION

Hi @anmolkatia 

 

just noticed you wanted a cummulative total, then 

 

Supposing you have the following data:

ActualDateDueDateGapAmt
10/5/202210/1/2022-410
10/28/202211/1/2022450
11/30/202212/1/2022170
12/26/20221/1/20236100
1/31/20232/1/2023110
2/20/20233/1/2023990
3/22/20234/1/20231090
4/27/20235/1/2023410

 

To get what you expect, you would need to:

 

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

GapSegmentMinMax
<0-5-1
<5-55
<10-510

(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:

FreemanZ_2-1672040729927.png

 

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

View solution in original post

9 REPLIES 9
FreemanZ
Super User
Super User

hi @anmolkatia 

 

Supposing you have the following data:

ActualDateDueDateGapAmt
10/5/202210/1/2022-410
10/28/202211/1/2022450
11/30/202212/1/2022170
12/26/20221/1/20236100
1/31/20232/1/2023110
2/20/20233/1/2023990
3/22/20234/1/20231090
4/27/20235/1/2023410

 

To get what you expect, you would need to:

 

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

GapSegmentMinMax
<0-5-1
0-505
6-10610

(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_0-1672040211685.png

 

 

Hi @anmolkatia 

 

just noticed you wanted a cummulative total, then 

 

Supposing you have the following data:

ActualDateDueDateGapAmt
10/5/202210/1/2022-410
10/28/202211/1/2022450
11/30/202212/1/2022170
12/26/20221/1/20236100
1/31/20232/1/2023110
2/20/20233/1/2023990
3/22/20234/1/20231090
4/27/20235/1/2023410

 

To get what you expect, you would need to:

 

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

GapSegmentMinMax
<0-5-1
<5-55
<10-510

(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:

FreemanZ_2-1672040729927.png

 

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

Thanks a Lot. Worked Flawlessly and Amazing Explaination. 

Mahesh0016
Super User
Super User

Mahesh0016_0-1672036594596.png

@anmolkatia 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.

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.

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

amitchandak
Super User
Super User

@anmolkatia , 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/1...


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

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.

@anmolkatia, 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

 amitchandak_4-1672038449573.png

 

 

 

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.