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

Accepted Solutions
Highlighted
Super User II
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.

excelwithallison.com

6 REPLIES 6
Highlighted
Super User II
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.

excelwithallison.com

Highlighted
Regular Visitor

Hi Alison,
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.

Highlighted
Super User II
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.

excelwithallison.com

Highlighted
Super User II
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.

excelwithallison.com

Highlighted
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

Highlighted
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!

💌

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors