Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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:
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
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.
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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |