Forum Discussion
accumulated table with specific format requests
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!
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.
3 Replies
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.biHi 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.
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.
