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. 

Mahesh0016's avatar
Mahesh0016
Super User
3 years ago

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's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Mahesh0016
      Super User
      3 years ago

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