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

 Category Value Nacional 113,240,040 Importador 106,828,146 Nacional 97,134,688 Importador 94,064,210 Importador 90,575,333 Nacional 35,319,980

And need to return this visual

Need 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
Super User III
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

6 REPLIES 6
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

Regular Visitor

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.

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

Super User III
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

Regular Visitor

Yeyy thank you.

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

💌

Super User III
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

Regular Visitor

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

## Helpful resources

Announcements

#### Happy New Year from Power BI

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

#### Check it Out!

Click here to read more about the December 2020 Updates!

#### 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

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

Top Solution Authors
Top Kudoed Authors