Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello all,
I have a problem that I can not solve
i want to find the % of purchase and no purchase on a matrix table
like for example
i would like to get a table like this
i want to have a % for each Purchase and no purchase for each month next the the "Nb" coulumn
Below is a sample image from Excel, i just wanted to get something similar
Sorry for my previous data info i guess this pbix would make more sense, please download from below link
Hi Kudo,
Thanks for your reply , below is my table and i need to get similar table like in the screenshot i provided earlier, is there any way you can help please
id | submissionNumber | userId | 📠Country | 📠City | 📠Retailer | 📠Store | 📆Date | 📝Was a service performed? | 📝Was the AOP kit used? | 📝Type of Service.1 | 📝Type of Service.2 | 📝Which Collection was used for the Service? | 📝Was the client's Complexion touched up? | 📝Was a Purchase made? | 🏷Number of References | 📦Total Quantity of Products | 🪙Total Basket Value | 🚻Was the client a new La Prairie user? | 🚻Client Gender | 🌎Nationality | 🚻Client Age Range | UpdateUser.Id | 📆Date - Basket | CorrectDate |
019c789c-9308-4125-9600-41ddb94820bf | 27955660 | 106012 | HONG KONG | HONG KONG | DFS | HONG KONG, DOWNTOWN DUTY FREE, GALLERIA SUN PLAZA | 7/21/2022 7:04:42 AM +00:00 | ❌No | ❌No | ❌No | Purchase | 1 | 1 | 281 | Existing | 🚺Female | Europe | 41-50 | 76912 | 7/21/2022 9:04:42 AM +02:00 | 7/21/2022 3:04:42 PM +00:00 | |||
03a1334a-9177-4e67-95cd-cb2b6453db83 | 27956667 | 106012 | HONG KONG | HONG KONG | DFS | HONG KONG, DOWNTOWN DUTY FREE, GALLERIA SUN PLAZA | 7/21/2022 7:46:47 AM +00:00 | ✅Yes | ❌No | Face | Full | Platinum Rare | ❌No | Purchase | 1 | 1 | 281 | Existing | 🚺Female | China | 31-40 | 7/21/2022 9:46:47 AM +02:00 | 7/21/2022 3:46:47 PM +00:00 | |
1f160837-65fc-4d58-987a-1e0ba5d3b8f3 | 27941425 | 105812 | UNITED ARAB EMIRATES | DUBAI | DUBAI DUTY FREE | DUBAI, DXB, DF, CONCOURSE A, ZONE 1 | 8/12/2022 6:44:12 AM +00:00 | ✅Yes | ✅Yes | Hand | Hand | Platinum Rare | ✅Yes | No Purchase | 0 | 0 | 0 | New | 🚺Female | India | 20-30 | 76912 | 8/12/2022 8:44:12 AM +02:00 | 8/12/2022 10:44:12 AM +00:00 |
203c9a5d-7022-4045-9047-05b20616fd8c | 27946335 | 106012 | HONG KONG | HONG KONG | DFS | HONG KONG, DOWNTOWN DUTY FREE, GALLERIA SUN PLAZA | 8/19/2022 8:00:00 AM +00:00 | ❌No | ❌No | ❌No | Purchase | 1 | 1 | 252 | New | 👥Other | Europe | 31-40 | 76912 | 8/19/2022 10:00:00 AM +02:00 | 8/19/2022 4:00:00 PM +00:00 | |||
25cbe64b-99fc-4f43-bdf6-65b4c83c04e4 | 27964488 | 106047 | UNITED KINGDOM | LONDON | DUFRY | LONDON, LHR, TERMINAL 5 | 7/20/2022 10:25:00 AM +00:00 | ✅Yes | ❌No | Hand | Hand | Skin Caviar | ❌No | No Purchase | null | null | null | New | 🚹Male | Other Asia | 41-50 | 7/20/2022 12:25:00 PM +02:00 | 7/20/2022 11:25:00 AM +00:00 | |
26a381f5-2c60-48d5-a210-0cd5f0ca1c7a | 27941441 | 105812 | HONG KONG | HONG KONG | DFS | HONG KONG, DOWNTOWN DUTY FREE, GALLERIA SUN PLAZA | 8/21/2022 6:15:39 AM +00:00 | ❌No | ❌No | ✅Yes | No Purchase | 0 | 0 | 0 | Existing | 🚺Female | Africa | 50+ | 76912 | 8/21/2022 8:15:39 AM +02:00 | 8/21/2022 2:15:39 PM +00:00 | |||
4f653761-e97a-4d26-8cf4-ea8b733c8221 | 27941418 | 105812 | UNITED KINGDOM | LONDON | DUFRY | LONDON, LHR, TERMINAL 3 | 8/11/2022 6:43:36 AM +00:00 | ✅Yes | ✅Yes | Eyes | Product Application | White Caviar | ❌No | No Purchase | 0 | 0 | 0 | New | 🚺Female | North America | 41-50 | 76912 | 8/11/2022 8:43:36 AM +02:00 | 8/11/2022 7:43:36 AM +00:00 |
6c693221-5163-4d83-b197-6b0ed0cd67a2 | 27941454 | 105812 | MACAO | MACAO | DFS | MACAO, DOWNTOWN DUTY FREE, FOUR SEASONS | 8/15/2022 6:45:06 AM +00:00 | ❌No | ❌No | ✅Yes | Purchase | 3 | 4 | 1008 | New | 🚹Male | South America | 20-30 | 76912 | 8/15/2022 8:45:06 AM +02:00 | 8/15/2022 2:45:06 PM +00:00 | |||
718722ec-5b1a-45d9-afca-199f600affa9 | 27941468 | 105812 | MACAO | MACAO | DFS | MACAO, DOWNTOWN DUTY FREE, FOUR SEASONS | 8/20/2022 6:46:13 AM +00:00 | ✅Yes | ✅Yes | Face | Full | Platinum Rare | ✅Yes | Purchase | 5 | 9 | 1860 | New | 👥Other | China | 41-50 | 76912 | 8/20/2022 8:46:13 AM +02:00 | 8/20/2022 2:46:13 PM +00:00 |
74a2e57a-3323-4480-a8b5-4de5e849a950 | 27946326 | 106012 | HONG KONG | HONG KONG | DFS | HONG KONG, DOWNTOWN DUTY FREE, GALLERIA SUN PLAZA | 8/9/2022 12:09:00 PM +00:00 | ✅Yes | ✅Yes | Face | Full | Platinum Rare | ✅Yes | Purchase | 2 | 2 | 2868 | New | 🚹Male | Middle East | 41-50 | 76912 | 8/9/2022 2:09:00 PM +02:00 | 8/9/2022 8:09:00 PM +00:00 |
85af6625-9d59-455e-b86b-96800d191265 | 27926058 | 106021 | UNITED KINGDOM | LONDON | DUFRY | LONDON, LHR, TERMINAL 2 | 8/4/2022 9:07:00 AM +00:00 | ✅Yes | ✅Yes | Face | Full | White Caviar | ❌No | Purchase | 2 | 8 | 1828 | New | 🚺Female | South America | 20-30 | 76912 | 8/4/2022 11:07:00 AM +02:00 | 8/4/2022 10:07:00 AM +00:00 |
9e656bd8-4852-4b2a-a714-724e9bfc287b | 27956721 | 106012 | HONG KONG | HONG KONG | DFS | HONG KONG, DOWNTOWN DUTY FREE, GALLERIA SUN PLAZA | 7/21/2022 7:48:54 AM +00:00 | ❌No | ❌No | ✅Yes | Purchase | 1 | 1 | 252 | Existing | 🚺Female | South America | 31-40 | 7/21/2022 9:48:54 AM +02:00 | 7/21/2022 3:48:54 PM +00:00 | ||||
a357e1b1-c42d-409d-9698-1bd0f42c39e1 | 27941411 | 105812 | UNITED STATES | LOS ANGELES | DFS | LOS ANGELES, LAX, TOM BRADLY TERMINAL INTERNATIONAL | 8/9/2022 6:42:48 AM +00:00 | ✅Yes | ✅Yes | Face | Full | Pure Gold | ✅Yes | Purchase | 3 | 6 | 1599 | New | 👥Other | Other Asia | 31-40 | 76912 | 8/9/2022 8:42:48 AM +02:00 | 8/9/2022 1:42:48 PM +00:00 |
b8211cfe-44ef-4dd7-bf67-202388af9f82 | 27964472 | 106047 | UNITED KINGDOM | LONDON | DUFRY | LONDON, LHR, TERMINAL 2 | 7/21/2022 10:23:50 AM +00:00 | ✅Yes | ✅Yes | Face | Prelude | Pure Gold | ✅Yes | Purchase | 2 | 2 | null | New | 🚺Female | Europe | 31-40 | 7/21/2022 12:23:50 PM +02:00 | 7/21/2022 11:23:50 AM +00:00 | |
d16cb1cd-9225-478d-8a2d-f956e1e35e00 | 27932435 | 106012 | HONG KONG | HONG KONG | DFS | HONG KONG, DOWNTOWN DUTY FREE, GALLERIA SUN PLAZA | 8/3/2022 2:49:00 PM +00:00 | ❌No | ❌No | Face | Prelude | Skin Caviar | ❌No | Purchase | 1 | 1 | 790 | New | 🚹Male | Middle East | 41-50 | 76912 | 8/3/2022 4:49:00 PM +02:00 | 8/3/2022 10:49:00 PM +00:00 |
e070b7c5-ac97-4a1f-a962-87b5878fede0 | 27942061 | 106012 | HONG KONG | HONG KONG | DFS | HONG KONG, DOWNTOWN DUTY FREE, GALLERIA SUN PLAZA | 8/10/2022 10:20:00 AM +00:00 | ✅Yes | ✅Yes | Face | Prelude | Skin Caviar | ❌No | Purchase | 2 | 3 | 2019 | Existing | 🚺Female | Russia | 31-40 | 76912 | 8/10/2022 12:20:00 PM +02:00 | 8/10/2022 6:20:00 PM +00:00 |
Try this code
Purchase = CALCULATE( DISTINCTCOUNT('Sales'[id]) , 'Sales'[Was a Purchase made?] = "Purchase")
No purchase = CALCULATE( DISTINCTCOUNT('Sales'[id]) , 'Sales'[Was a Purchase made?] <> "Purchase")
Purchase % = DIVIDE( [Purchase] , [Purchase] + [No purchase] )
You need provide more information if it won't help.
I suppose that It's not a one matrix table on screenshots. It's two matrix.
If you want just calculate measures you can use something like this:
Purchase = CALCULATE( COUNT('Sales'[purchase id]) , 'Sales'[Status] = "Purchase")
No purchase = CALCULATE( COUNT('Sales'[purchase id]) , 'Sales'[Status] <> "Purchase")
Purchase % = DIVIDE( [Purchase] , [Purchase] + [No purchase] )
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |