Sort amounts into groups/columns

[BlueSkyX]

I´m (more or less) an absolut PBI newbie and need some help.

I have a list with amounts and deadlines (in the past and upcoming) and calculated with DATEDIFF the corresponding due days (a few thousand values between 400 and -120). Now I would like to sort them into different groups/columns according to the due date.

• Sum for all entries with due days >1
• For due days "1-5"
• For "6-10"
• For "10-20"
  ......

I tried to rebuild in Excel how it should look like in the end (I have green part and trying to build the grey one):

In theory and Excel it is very easy  .... but not in PBI without the neccessary PBI knowledge. 😉 

I already tried to create a "conditional column" but I can not select the "due days" as it is a calculated column and not part of the Power Query Editor.

[Ritaf1983 · Accepted solution]

Hi @BlueSkyX 
If Due Days is a column you can use bins, please refer to the linked tutorial :
https://tic.li/c/51714/3Dm72QxG6J

If it is a measure you can use workarounds of the segmentation:

https://www.youtube.com/watch?v=0KBR-aEVaxk

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[BlueSkyX · Accepted solution]

Thanks for your support!

Meanwhile I adjusted the data table a little bit and integrated xthe calculation of the due days with a measures. This step enables the possibility to insert the groups by "conditional column". Maybe not the professional way but for me the most logical one. 😉

[Anonymous]

Hi @BlueSkyX 

You can consider to create a calculated column with siwtch() funcion.

e.g 

Type =
SWITCH (
    TRUE (),
    [Due Days] >= 1
        && [Due Days] <= 5, "Due 1-5",
    [Due Days] >= 6
        && [Due Days] <= 10, "Due 6-10",
    [Due Days] >= 11, "Due >11"
)

Then you can put them into a matrix visual

e.g 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ritaf1983 · Accepted solution]

Hi @BlueSkyX 
If Due Days is a column you can use bins, please refer to the linked tutorial :
https://tic.li/c/51714/3Dm72QxG6J

If it is a measure you can use workarounds of the segmentation:

https://www.youtube.com/watch?v=0KBR-aEVaxk

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly