Forum Discussion
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
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.
9 Replies
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-kHello 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.
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
Anonymous Create Calculated Column Days.
Days = DATEDIFF(Dim_Date[Date],MAX(Dim_Date[Date]),DAY)ThenDelay 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.
Anonymous Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
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:
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.
