Power BI Matrix

[Anonymous]

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 customer, together with the invoice amount and invoice unit

**GB = gigabyte, HRS = hours 

And I would like to create a matrix that looks like this:

When I do this in Power BI, it only shows the 'First Invoice Unit' (GB in this case). And GB is of course not applicable for services by the hour. 

I guess this means tricking Power BI into not doing any sums on the 'Invoice Unit' field, but simply look at what is the only possible invoice unit when the service is backup (that would be GB), and what is the only possible invoice unit when the service is hours (that would be HRS). But I'm not sure if that's even possible. 

Thanks in advance!

Best regards,

Ruxandra

[v-diye-msft]

Hi @Anonymous 

If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly.thanks!

[MFelix · Accepted solution]

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]

Hi @MFelix ,

You were right with this. I'm not sure what happened, but Power BI only showed one invoice unit for all services (as can be seen in my screenshot), even though each service had its unique single invoice unit. At some point, it just changed to the correct distribution, but i have no idea why. No changes were done in the data source. 

But anyway, thanks a lot!

Regards,

Ruxandra 

[Anonymous]

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]

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]

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:

[Greg_Deckler]

I don't see your original table.

[Anonymous]

Hello,

That should be fixed now, thanks for the notice!