cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Super User

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.

9 REPLIES 9
Super User

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:

Super User

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.

New Member

Thanks a Lot. Worked Flawlessly and Amazing Explaination.

Super User

@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.
New Member

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.

Super User

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

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

New Member

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.

Super User

@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

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors