accumulated table with specific format requests

[Anonymous]

Dear all

I am wondering how to make the visual below happen. Below is a dummy case I made:

I have three filters

Company Name

Pepsi

Coke

Month

Jan

Feb

Mar

Apr

May

Country

United States

Japan

Russia

Turkey

China

A master table included all orders

Company Name	Order_No	Year	Month	Order_Country
Pepsi	P01	2020	Jan	United States
Pepsi	P02	2020	Feb	Japan
Pepsi	P03	2020	Mar	China
Pepsi	P04	2020	Feb	United States
Pepsi	P05	2021	Mar	Japan
Pepsi	P06	2021	Mar	United States
Coke	C01	2021	Apri	Russia
Coke	C02	2021	May	Russia
Coke	C03	2021	May	Turkey
Coke	C04	2021	May	China

I want to get the accumulated count of orders by country and year, and the country names have to appear regardless there is any value. (but country names will only show up while corresponding countries are selected.)

Below are the examples:

If I selected Pepsi and January:

Once I selected Feb, since there are two orders in the United States since Jan 2020, so the value becomes 2.

If I select March, China will get 1 in 2020 and Japan get 1 in 2021, and the rest of the table stays the same because it is an accumulated table.

As you can see, Russia and Turkey for another brand never pop up.

Seems an easy case but I just cannot figure it out.... please help!!!

Also, is it possible just to create a measure (i.e. no new table in this case) to make it happen?

Many thanks!

[selimovd]

Hey @Anonymous ,

you can show them with the "show items with no data" option:

https://docs.microsoft.com/power-bi/create-reports/desktop-show-items-no-data

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍.

Best regards
Denis

Blog: WhatTheFact.bi

[Anonymous]

Hi @selimovd thanks! But I have tried this feature, and it will return the country of other companies. In this case above, Russia and Turkey will show in the table too and this is not exactly I want.

[Anonymous · Accepted solution]

I feel like I found the answer:

I created a new table called Month_filter (the data type of "Month_No" is "value")

and did not connect this table to the master table

Then, I created an extra column "Month_No" in the master table and set the data type as "value".

Then, I created a measure as below:

CALCULATE(COUNT(Order_No), ALLEXCEPT(master table, Company_FILTER,YEAR,), FILTER(master table, MONTH_No >=1), FILTER(master table, MONTH_No <=SELECTVALUE(Month_FILTER))

P.S. the DAX above is just a concept and may not work properly.