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
admin11
Memorable Member
Memorable Member

How to compute Brand by SOURCE ?

HI All

 

@parry2k help me with the below post :-

 

https://community.powerbi.com/t5/Desktop/How-to-use-DAX-expression-to-count-number-of-brand-sales-gt...

 

Now i manage to compute SOURCE = TA Total have 10 Brand.

 

admin11_0-1641182983292.png

Now i also know that SOURCE = TP total have 8 Brand :-

admin11_1-1641183037571.png

May i know how can i get the summary report like below :-

admin11_2-1641183114824.png

 

Below link is my PBI sample file :-

https://www.dropbox.com/s/c4d4uxcpoiaizyb/PBTI_V023%20source%20by%20brand.pbix?dl=0

 

Hope spme one can share with me.

 

Paul

10 REPLIES 10
v-shex-msft
Community Support
Community Support

Hi @admin11,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
smpa01
Super User
Super User

@admin11  you can simply use this

Measure = CALCULATE(DISTINCTCOUNT(SALES_T[BRAND_C]),ALLEXCEPT(SALES_T,SALES_T[SOURCE]))

 

smpa01_0-1641232284039.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
parry2k
Super User
Super User

@admin11 that's not my measure, this is what I gave you, but you have something totally different:

 

Measure = COUNTROWS ( VALUES ( SALES_T[BRAND_C] ) )

 

Follow us on LinkedIn and subscribe to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k 

 

Yes , i just check , you give me the correct expression . now it working fine. 

 

Can i have one more request , that is add one more condition , only count those brand sales >100K only.  As i want to focus on high sales  brand.

admin11_0-1641231557632.png

Hope you can advise me.

 

Paul

 

@admin11 add this measure

 

Measure = 
SUMX ( 
    SUMMARIZE ( 
        SALES_T,
        SALES_T[SOURCE], 
        SALES_T[BRAND_C], 
        "@Sales", [SALES_T] //replace SALES_T with the measure you want to check for the value 
    ), 
    IF ( [@Sales] > 100000, 1 ) 
)
    
    // COUNTROWS ( VALUES ( SALES_T[BRAND_C] ) )

Follow us on LinkedIn and subscribe to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k 

Appreciate very much for your effort. Thank you.

i have apply your expression in my PBI , i realise that the limit setting 100K is not filter at actual value. there are some error. not sure cause by what ?

admin11_0-1641263397350.png

PBI sample file :-

https://www.dropbox.com/s/wknx581wrrui4vy/PBS_VER0293.pbix?dl=0

 

Paul

parry2k
Super User
Super User

@admin11 I think it should be a simple unique values of brand count per source

 

Measure = COUNTROWS ( VALUES ( SALES_T[BRAND_C] ) )

Follow us on LinkedIn and subscribe to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture.

 

Picture1.png

 

Brand Rank Count: =
CALCULATE (
COUNTROWS ( VALUES ( SALES_T[BRAND_C] ) ),
FILTER ( SALES_T, SALES_T[SOURCE] IN { "TA", "TP" } )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


@Jihwan_Kim  good solution if you want to filter on these two sources but using FILTER is not recommended since it is an iterator it will be very slow when working with large data. Just my 2 cents. The measure should look like this:

 

Brand Rank Count: =
CALCULATE (
COUNTROWS ( VALUES ( SALES_T[BRAND_C] ) ),
SALES_T[SOURCE] IN { "TA", "TP" }
)

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k 

I use your expression , every row return 11 ?

admin11_0-1641205047401.png

Where i go wrong ? Can you pls share with me.

 

Paul

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.