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
Skemaz
Advocate II
Advocate II

Create visual from filtered data

Hi

In the screen-shot below, I'm trying to create a pie chart (or similar) from my filtered data.

(1) The filtered numbers are being displayed in the "Card with States" by OKViz.

(2) & (3) are the cards.

(4) 3 Jockey/Trainer combinations contibuted to the 16 shown in (3).

The charts I need to show would be 384/16 as a percentage and 16/3 as a percentage.

Any ideas on how to acheive this will be appreciated.

Thanks, Mark.

Picture1.jpg

1 ACCEPTED SOLUTION

Hi

Thanks for helping.

What wound up working for me was the following:

primaryFiltered = CALCULATE(COUNT(Sheet1[Colour]), ALLEXCEPT(Sheet1, Sheet1[Substance], Sheet1[Population]))

secondaryCount = COUNT(Sheet1[Colour])

Regards, Mark.

View solution in original post

9 REPLIES 9
Abduvali
Skilled Sharer
Skilled Sharer

Hi @Skemaz,

 

You can use a calculated measure to achieve what you need but I need some clarity on points below to help you out:

  • Where the following values are coming from 384 and 16???
  • Are they in different columns?
  • And how do you differentiate between horses and race values?
  • 16/3....what does number 3 represent???

 

 

Regards

Abduvali 

Hi Abduvali

Thanks for your prompt reply.

The user selects filters in the "Race Filters" this reduces the number shown in (2) in the screen-shot. The number is just a single value in a Card. I have edited the filters interactions so that the filters in "Race Filters" apply to card (2) and (1).

However, the filters in "Horse Filters" only affect (1) and not (2).

The value could come from any field, but I have used a count of the "age" field to get the number.

In the screen-shot, (4) shows 3 rows representing the Jockey and Horse combination which is the 3 you asked about.

Thanks, Mark.

@Skemaz,

 

 

OK then create 4 measures:

  1. Race Measure = Calculate(SUM(YourTable[YourValue]),YourTable[RaceColumn] = "Race")
    1. this will let you get you 384 value, its same as the way you filtering it only by Race in that card
  2. Horse Measure = Calculate(SUM(YourTable[YourValue]),YourTable[RaceColumn] = "Horse")
    1. this will let you get you 16 value, its same as the way you filtering it only by Horse in that card
  3. Then create 3rd measure:
    1. % Measure  = [Race Measure] / [Horse Measure]
      1. then select display as Percentage from Modelling tab / Data type
  4. For the last one you will need another measure:
    1. Jockey Measure = [Horse Measure] / Distinctcount(YourTable[JockeyColumn])
      1. then select display as Percentage from Modelling tab / Data type

 

I hope this will help if you have any questions let me know.

 

 

Regards

Abduvali

Hi

Thanks for helping.

What wound up working for me was the following:

primaryFiltered = CALCULATE(COUNT(Sheet1[Colour]), ALLEXCEPT(Sheet1, Sheet1[Substance], Sheet1[Population]))

secondaryCount = COUNT(Sheet1[Colour])

Regards, Mark.

Hi Abduvali

Please review the screen-shots of my attempt to apply your proposed solution in the link below.

You advice will be greatly appreciated.

https://1drv.ms/i/s!Ai8u-AHGz6m3iax60HZWwL66RSxy9w

Description:

(1) "age" is a text field so I get the error shown in (1).

(2) I switch to a numeric field "race" (see (3) in screen-shots) and get a different error.

(4) I replace SUM with COUNT and in (5) I get a Blank result.

(6) I remove  = "Race" from the measure and I get a result of 828 which matches my filter result 🙂

(7) I filter and both results match (384) - still all good 🙂

In the last screen-shot, (8) I select a filter that has interactions of filters set to not affect the card in (9).

It only impacts (10).

However it does impact the measure - see (11).

I need (11) to match up with (9).

Any ideas on how to do this?

Thanks, Mark.

@Skemaz,

 

Can you provide some sample data? or you pbix to look at it?

  • I'm also interested to know whats in your RACE column
  • or at least provide a screenshot of your table view

 

 

Hi

I created a simple report (.pbix at the link below) to explain my requirement better,

Also, please check out these screen-shots with explainations below.

Thanks, Mark.

This is the link to  zip file containing the .pbix and data source click here

 

Picture3.jpg

This larger image may be easier to read 😉Pic1.png

 

Thank you very much.
I'll check it out when I get back to my computer.
Greatly appreciated.
Regards, Mark.

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.