cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Average value form a filter column filter by categories

So I have a table that have a column with values and an other with the category "National" or "International". I need to calculate the average percentage of the values in the national category! And only return the value in % so that i can see it on a card.

This is the kind of data i have

CategoryValue
Nacional113,240,040
Importador106,828,146
Nacional97,134,688
Importador94,064,210
Importador90,575,333
Nacional35,319,980
 

And need to return this visual

Need to get back this visualNeed to get back this visual

 

I have done this

National =CALCULATE(COUNTA('SEGUIMIENTO'[Category]),'SEGUIMIENTO'[Value] IN { "Nacional" })

%National =DIVIDE([National], COUNTA('SEGUIMIENTO'[Value]))

 

But it does not retun the correct answer

 
1 ACCEPTED SOLUTION

I'm on a phone so sorry for the short answer, but change the countrows to sumx. You will need an extra argument:

%National 'DIVIDE(SUMX(FILTER(ALL('FOLLOW'), 'FOLLOW'[Category] ? "National"),
'Tracking'[value]
)
,
SUMX(ALL('TRACKING') , Tracking[value])
)

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

View solution in original post

6 REPLIES 6
Super User III
Super User III

Try:
%National =DIVIDE(CALCULATE(COUNTROWS(FILTER(ALL('SEGUIMIENTO'), 'SEGUIMIENTO'[Category] = "Nacional"), CALCULATE(COUNTROWS(ALL('SEGUIMIENTO')
)

You could replace ALL with ALLSELECTED depending on if you want this percent to update with slicer selections.

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Hi Alison,
thanks for answering.
But the answer gives me a percentage of 96% and it should be 86 there is something that power bi is not understanding me 😂. I need it not to count the rows but the percentage of the total money. I don't know if you understand me. If i do it in a pie it shows me the correct answer.

Captura de pantalla 2020-09-20 123940.jpg

 

But, muy supervisor wants to see it on a card. 

 

I'm on a phone so sorry for the short answer, but change the countrows to sumx. You will need an extra argument:

%National 'DIVIDE(SUMX(FILTER(ALL('FOLLOW'), 'FOLLOW'[Category] ? "National"),
'Tracking'[value]
)
,
SUMX(ALL('TRACKING') , Tracking[value])
)

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

View solution in original post

Yeyy thank you.

Im new on this matter i've learned all by myself but at work i always find something more challenging stuff!

💌

What have you put in values for that pie chart?

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

I have it in spanish but,

  • In the legend space I have "category"
  • In the values space I have "Value or $"

Like you see in the image below

Captura de pantalla 2020-09-20 150004.jpg

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors