Group columns from a different tables, filter it and have SUM of values in the end

[Anonymous]

Hello.

I need some help with creating a table visual with next columns and measures:

1. Corporate Account (Store Table) - Column

2. Management Company (Store Table) - Column

3. Brand Variety (Product Table) - Column

4. Date (Date or Fact Table) - Column

5. # of Stores - Measure

6. # of Stores "In Compliance" - Measure

7. # of Stores "Out of Compliance" - Measure

The idea is:
For each Management Company there should be specific Brand Variety values that has a Volume 9L.
For instance, for the Management Company (Aimbridge - AC Hotels) 3 Brand Variety (ON THE ROCKS EFFEN COSMO, ON THE ROCKS KNC OLD FASHION and a choice of ON THE ROCKS HORN MARG or ON THE ROCKS LARIOS AVIATION) should have any volume (Volume 9L > 0) and then call it "In Compliance", otherwise "Out of Compliance". The last part of it calculate how many Stores for a specific Management Company are in Total, "In Compliance" and "Out of Compliance" for a specific time period.

Below attached screenshot with a data look.

Thank you for any help!

[amitchandak]

@Anonymous , Consider measures

Compliance Type= if(Sum(Fact[VOlumn 9L](>0, "In Compliance", "Out of Compliance")

Count of Compliance =
Countx(FIlter(Summarize(Fact, Store[Corporate Account], Store[Management Company],Product[Brand Variety], Date[date],"_1",Sum(Fact[VOlumn 9L])), [_1]>0),[Corporate Account])

if you need a slicer, consider

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k