stack bar chart

[Anonymous]

Hello, I have to create a Stacked bar chart shown in the picture, which shows data from the last 30 days last 60 days, and so on. and I am uploading the data sample too. 

I have tried a lot by creating columns, using other tips and tricks but couldn't succeed. Please kindly show me any way to do it. I would really appreciate it. 

Thanks.

[HarishKM]

@Anonymous  Create a conditonal coloumn in power query from date open coloumn and use below formula ti get today date .

Date.From(DateTime.LocalNow()

then subtract today - exact date from table  ( [Date today] - [Date] )

it will give you total day then create a coditional column .

kindly refer this image 

output result 

output pbi

@Anonymous  Kindly check and let me know .

Have you tried this ?

try this and let me know 

Kudos will be appriciated   😊

[Jihwan_Kim]

Hi, @Anonymous 

I assume you have the below grouping table.

Please check the below measure.

Once the below measure is inserted into the visualization, then you can segment by group column in the grouping table.

If it is OK with you, share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

 

Values Total by group =
SUMX (
FILTER (
VALUES ( 'Calendar'[Date] ),
COUNTROWS (
FILTER ( 'Group', [Days Open] >= 'Group'[Min] && [Days Open] < 'Group'[Max] )
) > 0
),
[Values Total]
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

I have already shared with you the PBX file link. 

here it is 

https://drive.google.com/file/d/1v1DTWwFXCRTma_zeS3jBA4MsSiMiFckw/view?usp=sharing

@Jihwan_Kim 

[Jihwan_Kim]

Hi, @Anonymous 

Sorry that I could not find the link before.

Now, I see the link. However, it asks to enter a password.

Please kindly check.

Thank you.

[Anonymous]

hi @Jihwan_Kim , sorry for that. I hope it will work now.

thank you. 

https://drive.google.com/file/d/1v1DTWwFXCRTma_zeS3jBA4MsSiMiFckw/view?usp=sharing

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for sharing the data.

Please check the below picture and the pbix file's link down below, whether it is what you are looking for.

It is quite difficult for me to fully understand your case because there is some blank information inside the table. 

And I want to suggest try to write measures and put measures into the visualization, instead of only pulling the actual column (=number type column) into the visualization.

https://www.dropbox.com/s/u5pzrdyz3r2fogn/pbx%20file.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Hi, @Jihwan_Kim, Thank you for the efforts but this is not working for me 😞

[Anonymous]

Hi,  @Jihwan_Kim thanks for answering. No, I don't have such a grouping table. and the visual I have shared here is not from PBX. probably in excel. this is a sample I have to create the same thing in Power BI. 

so can you describe to me how I can create this grouping table?

Thanks 

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

You can create the grouping table exactly the same looking as the above picture that I attached.

If you can share your sample excel file's link here, I can try.

Thanks.

[Anonymous]

Hi, @Jihwan_Kim , Thank you for your reply. 

This grouping table can be created by using a new table? and unfortunately, I don't the axis. 

Before posting this query I tried creating 4 conditional columns in PBI and then created the stacked graph. but it didn't work.  I am attaching the screenshot of the columns.

please have a look.

Thank you. 

[Jihwan_Kim]

Hi, @Anonymous 

You can create the grouping table in excel and import it.

Or, you can create the grouping table in power bi manually, like what you do in excel.

It is only a small-size table, and I think there are not many difficulties in creating the grouping table.

And this grouping table already has all the conditions you need. So, you can just simply use this grouping table as a filtering condition.

[Anonymous]

hi, @Jihwan_Kim 

can you please explain how can I use it in the filtering condition?

Thank you. 

[Jihwan_Kim]

Hi,

As I already mentioned in the previous reply, you can create the measure below.

And put the measure into the visualization.

Values Total by group =
SUMX (
FILTER (
VALUES ( 'Calendar'[Date] ),
COUNTROWS (
FILTER ( 'Group', [Days Open] >= 'Group'[Min] && [Days Open] < 'Group'[Max] )
) > 0
),
[Values Total]
)

[Anonymous]

Hi, @Jihwan_Kim 

I am really sorry to bother a lot. but I am a newbie in power BI. 

I created the table but to use it I need to link it with my existing data model, right? how I join both tables? because I can not create the measure in the group table because the date and open days are in a different table.

Thank you so much.  

[Jihwan_Kim]

Hi,

As I mentioned, if you can share your sample file, it will be much easier to understand by seeing what I can suggest doing in PBI Desktop than just reading words without any sample data. I failed to create sample data based on your explanation. If it is not allowed to share your sample data, then please create one by excel. Thanks.

[Anonymous]

@MFelix @Greg_Deckler @amitchandak 

[Anonymous]

Hi, @Jihwan_Kim I am attaching a link of sample pbi file. you can see the data and can estimate what kind of solution can work. 

https://drive.google.com/file/d/1v1DTWwFXCRTma_zeS3jBA4MsSiMiFckw/view?usp=sharing

THank you.