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
apenaloz
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])
)

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

6 REPLIES 6
AllisonKennedy
Super User
Super User

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.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, 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])
)

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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?

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, 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
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.