cancel
Showing results for 
Search instead for 
Did you mean: 
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!






New Animated Dashboard: Sales Calendar


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.






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.





admin11
Memorable Member
Memorable Member

@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.






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.





admin11
Memorable Member
Memorable Member

@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.

 






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" }
)

 






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.





admin11
Memorable Member
Memorable Member

@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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors