Forum Discussion

nkrajacic's avatar
nkrajacic
Advocate I
6 years ago
Solved

Creating Matrix of Calculated Value Ranges

Hello, I am attempting to replicate a matrix similar to that below which calculates a vendor's spend for a specified time period and then classifies the suppliers into spend ranges. I believe I need...
Need Help
Show and Tell
Tips and Tricks
  • JarroVGIT's avatar
    JarroVGIT
    6 years ago
    Try replacing COUNTROWS(_filteredTable) with COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( _filteredTable, "col", [VendorColumnName] ) ) )
nkrajacic's avatar
nkrajacic
Advocate I
6 years ago

JarroVGIT my supplier counts are very high now. See DAX below. Any idea what I may have goofed up on...

 

# Suppliers =
VAR _summaryTable = SUMMARIZE(ALL('BI LOAD TAB'), 'BI LOAD TAB'[Supplier Name], "Total Spend", SUM('BI LOAD TAB'[Spend (USD)]))
VAR _curMin = SELECTEDVALUE(RangeTable[Min])
VAR _curMax = IF(SELECTEDVALUE(RangeTable[Max]) = BLANK(), MAXX(_summaryTable, [Total Spend]), SELECTEDVALUE(RangeTable[Max]))
VAR _filteredTable = FILTER(_summaryTable, [Total Spend] >= _curMin && [Total Spend] <= _curMax)
RETURN
COUNTROWS(_filteredTable)
JarroVGIT's avatar
JarroVGIT
Resident Rockstar
6 years ago

Hi nkrajacic 

Sorry to hear it still isn't working for you. Your DAX is fine, it should work. I have tested it myself with test data. Perhaps you would benefit of having a look at my pbix file?

https://1drv.ms/u/s!Ancq8HFZYL_aiIliJsgQRYrQVSZMEA?e=QYSpv4

The relevant tables are 1RangesTable and 2TableTest and my visual is on Page 2. you can ignore the other tables and pages, these are for other topics in this community 🙂 Let me know if you have any questions!

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

  • nkrajacic's avatar
    nkrajacic
    Advocate I
    6 years ago

    JarroVGIT  is there any way that the measure could be ignoring filters that I have established? The new supplier total looks like it could be very similar to the supplier count for all of the categories in the raw data file. I have it filtered for only my category.

  • JarroVGIT's avatar
    JarroVGIT
    Resident Rockstar
    6 years ago
    Ah yes this does ingore external filters, if you don't want that you can remove ALL(0 statement in first variable (in both measures) but leave the tablename there as first argument of SUMMARIZE
  • nkrajacic's avatar
    nkrajacic
    Advocate I
    6 years ago

    BINGO!! We got it! Thanks again!

  • nkrajacic's avatar
    nkrajacic
    Advocate I
    6 years ago

    JarroVGIT yep, this all makes sense especially after viewing your example. I am very puzzled why my supplier count is still out of whack. Especially since the second DAX which calculates total spend seems to be working just fine.