Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Power BI Matrix

Hello, I am trying to create a matrix and I was wondering if it is possible to use a text column as a value in a matrix.  My table looks like this: *this shows services offered to each custo...
Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi Anonymous ,

     

    If the detail of the Invoice unit has only one value per each Service then when you select the first or the last value in the matrix should return the expected value, however if for Hours you have more than one Invoice unit then you will get incorrect values.

     

    How does your data model looks like?

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi MFelix 

Each service (Asset_Subtype below) has only one Invoice Unit (e.g. storage space can only be sold in GB).

"None" for example has indeed more categories with different invoice units, but i filtered that out and the problem persists. 

What I want to do in Power BI is this (small orange box is a customer name and the big box covers amounts):

 

So maybe it's not because a service has more invoice units, but a customer has more services which automatically implies more invoice units per customer? 

Are you aware of any workaround?

Thanks!

Kind regards,

Ruxandra 

MFelix's avatar
MFelix
Super User
6 years ago

Hi Anonymous ,

 

What is the setup between the Asset_subtype and your invoice table? do you have a relationship between them?

Do you save the asset subtype and units on the invoiced table?

 

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    hI Anonymous ,

     

    One workaround can be to create a concatenated value with the values and units something similar to this:

    Measure = 
VAR temp_table =
    SUMMARIZE (
        Sales;
        Sales[asset_subtype];
        Sales[invoiced_units];
        "@Sales"; SUM ( Sales[Amount] )
    )
RETURN
    IF (
        HASONEFILTER ( Sales[asset_subtype] );
        CONCATENATEX (
            temp_table;
            [@Sales] & " " & Sales[invoiced_units];
            UNICHAR ( 10 )
        );
        SUM ( Sales[Amount] )
    )

     

    I'm assuming that the sales tabel has asset and units.

     

    Result will be this: