Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Measure: Division from two distinct values from two columns

Hi,

 

I have two tables:

 

SALES

idproductcart_idweek
1A9025
2B9025
3C9127
4A9127
5A9127

 

 

SEPCIAL_SALES

idproductcart_idweek
17A23425
18B23425
18C23525
19A23627
20C23627

 

Now I want to divide the distinct count of the card_id like:

 

COUNT(DISTINCT(SALES[card_id])) / COUNT(DISTINCT(SEPCIAL_SALES[card_id]))

 

And the results should be plotted per week. 

 

In this case:

 

251/2
271/1 

 

How do I do that? 

 

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

Hi, @Anonymous 

 

Create a bridge table using the week field, and establish a one-to-many relationship with the other two tables

v-angzheng-msft_0-1623823235031.png

then  try to create measures like below:

_CountOfSales =
CALCULATE (
    DISTINCTCOUNT ( 'SALES'[cart_id] ),
    FILTER ( ALL ( 'SALES' ), [week] = MAX ( 'bridgeTable'[Week] ) )
)
_CountOfSepcialSales =
CALCULATE (
    DISTINCTCOUNT ( SEPCIAL_SALES[cart_id] ),
    FILTER ( ALL ( 'SALES' ), [week] = MAX ( 'bridgeTable'[Week] ) )
)
_Result =
FORMAT ( 'Mea'[_CountOfSales], "General Number" ) & "/"
    & FORMAT ( 'Mea'[_CountOfSepcialSales], "General Number" )

Sample:

v-angzheng-msft_2-1623823455449.png

result:

v-angzheng-msft_1-1623823439516.png

Please refer to the attachment below for details

 

 

Hope this helps.

 

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-angzheng-msft
Community Support
Community Support

Hi, @Anonymous 

 

Create a bridge table using the week field, and establish a one-to-many relationship with the other two tables

v-angzheng-msft_0-1623823235031.png

then  try to create measures like below:

_CountOfSales =
CALCULATE (
    DISTINCTCOUNT ( 'SALES'[cart_id] ),
    FILTER ( ALL ( 'SALES' ), [week] = MAX ( 'bridgeTable'[Week] ) )
)
_CountOfSepcialSales =
CALCULATE (
    DISTINCTCOUNT ( SEPCIAL_SALES[cart_id] ),
    FILTER ( ALL ( 'SALES' ), [week] = MAX ( 'bridgeTable'[Week] ) )
)
_Result =
FORMAT ( 'Mea'[_CountOfSales], "General Number" ) & "/"
    & FORMAT ( 'Mea'[_CountOfSepcialSales], "General Number" )

Sample:

v-angzheng-msft_2-1623823455449.png

result:

v-angzheng-msft_1-1623823439516.png

Please refer to the attachment below for details

 

 

Hope this helps.

 

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@Anonymous , You need to create common tables like a week, product, and card id and analyze data with these common dimensions

 

Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19

 

You need to be in star schema

https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

Anonymous
Not applicable

This is not helpful.

 

The solution was to build a quickmeasure in the plot. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.