Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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+        ...
Need Help
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    Hi Anonymous 

     

    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:

     

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

FreemanZ's avatar
FreemanZ
Super User
3 years ago

Hi Anonymous 

 

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:

 

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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thanks a Lot. Worked Flawlessly and Amazing Explaination.