Splitting data

[Kirsten87]

Hi Everyone,

This is possibly a Friday afternoon question, but I'm an inexperienced user and struggling a little! I suspect that I could do this in the Power Query and by setting up relationships and or grouping, but I am not very strong at this yet and need a little guidance please! 

This is what I am trying to do:

Sample data:

Region	Country	Brand
APAC	China	Brand 1
APAC	China	Brand 2
APAC	Singapore	Brand 1
APAC	Hong Kong	Brand 3
APAC	Malaysia	Brand 3
APAC	China	Brand 2
APAC	Vietnam	Brand 1

So basically I want to have a slicer on the report that allows me to see the APAC countries excluding China and including all Brands, and then China on it's own, but excluding Brand 2 from the China results. How can I set this up?

Thank you in advance for entertaining my ignorance!

[Ashish_Mathur]

Hi,

Exactly what will you be selecting in the slicer and what result do you want to see in the Table visual?  Also, take atleast 1 more region in your table so you can show the result when a non APAC region is selected. 

[Ritaf1983 · Accepted solution]

Hi @Kirsten87 
According to the logic you described, I would add a classified column to the view.
Of course, you can change the names I gave you according to what you want to see.

The text of M formula :
if [#"Region (original)"]="APAC" and [Country]="China"
then
"APAC (Just China)"
else
if [#"Region (original)"] = "APAC" then
"Apac (exclude China)"
else
[#"Region (original)"]
Result:

Link to Sample File 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.